Imagine a three column dataset as shown below
| ID | Prize | Year |
| A | X | 9 |
| A | Y | 10 |
| B | X | 9 |
| B | Y | 9 |
| B | Z | 10 |
As you can observe, there are duplicates that appear in each column. The task it to convert this tabular data structure to a matrix like one with ID’s appearing down one column, Years appearing in one row and at each intersecting cell, the prizes should appear. Please note that there can be multiple prizes for each ID and year combination – ID is B and year is 9 in two rows but the prizes are different.
The expected solution is shown below
| ID | 9 | 10 |
| A | X | Y |
| B | X,Y | Z |
I have solved this problem by using Power Query. You may download the workbook from here.
You may also watch a short video here
Show multiple text entries in one cell of a Pivot Table
{ 0 Comments }