Here is a simple three column dataset showing Item ID, Catalogue Name and Catalogue Year
| Item ID | Catalogue Name | Catalogue Year |
| A | Spring | 2012 |
| A | Spring | 2016 |
| A | Spring | 2017 |
| B | Fall | 2014 |
| B | Fall | 2014 |
| B | Fall | 2016 |
The second and third last rows are clear duplicate rows. The task is to create a fourth column (titled as All Year) in this dataset with concatenated entries from the Catalogue Year column for each Item ID. So the expected result is:
| Item ID | Catalogue Name | Catalogue Year | All Year |
| A | Spring | 2012 | 2012, 2016, 2017 |
| A | Spring | 2016 | 2012, 2016, 2017 |
| A | Spring | 2017 | 2012, 2016, 2017 |
| B | Fall | 2014 | 2014, 2016 |
| B | Fall | 2014 | 2014, 2016 |
| B | Fall | 2016 | 2014, 2016 |
Notice that for Item ID B, the result in every cell of the fourth column is 2014, 2016 and not 2014,2014,2016. So while joining (concatenating in technical MS Excel terminology) entries from the “Catalogues Year” column in the “All Year” column, only unique entries for that Item ID have to be considered.
Excel 2016 has a TEXTJOIN() function which gets use close to the final result but does not take into account the unique entries only (it takes all entries into account all entries). On using the TEXTJOIN() array formula in a spare column, here is the result i got:
| Item ID | Catalogue Name | Catalogue Year | All Year |
| A | Spring | 2012 | 2012, 2016, 2017 |
| A | Spring | 2016 | 2012, 2016, 2017 |
| A | Spring | 2017 | 2012, 2016, 2017 |
| B | Fall | 2014 | 2014, 2014, 2016 |
| B | Fall | 2014 | 2014, 2014, 2016 |
| B | Fall | 2016 | 2014, 2014, 2016 |
Notice that for Item ID B, the result is 2014, 2014, 2017 and not 2014, 2017. So the TEXTJOIN() function does not work.
I have solved this problem using Power Query (Get and Transform in Excel 2016). You may download my solution workbook from here.
Combine unique entries from a range of cells after satisfying a condition
{ 2 Comments }