Show multiple text entries in one cell of a Pivot Table


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

I have solved this problem by using Power Query.  You may download the workbook from here.

You may also watch a short video here

Leave a Comment

Your email address will not be published.