Use pivot table to list values and add as comments

[MarkPhil]

I've been Googling and reading these forums for a while but still haven't figured out how to accomplish what I want to do. I'm not sure how to describe what I need in a way that's both concise and clear, so let me show you what I've got, and explain step-by-step. My data look like this:

	A	B	C	D
1	ID#	Name	Favorite Sport	Favorite Color
2	1	Angela	Golf	Blue
3	2	Bill	Golf	Blue
4	3	Chris	Basketball	Red
5	4	Denise	Basketball	Red
6	5	Eric	Golf	Red
7	6	Frances	Hockey	Red

<tbody>
</tbody>

I have a pivot table with "Favorite Sport" as the rows, "Favorite Color" as the columns, and "Count of ID#" as the values. So it looks like this:

	A	B	C
1		Blue	Red
2	Basketball		2
3	Golf	2	1
4	Hockey		1

<tbody>
</tbody>

What I want is to have a separate worksheet that shows the results of the pivot table, plus has a comment in each non-blank cell showing a list of the corresponding values from the Names column. So there would be a comment in B3 with the text "Angela, Bill", a comment in C2 with "Chris, Denise", a comment in C3 with "Eric", and so on.

I know that I can double click on a cell in the pivot table (or right-click and select “Show detail”) to get a new sheet with all the details for that cell, so I could get the Name values that way. But my data have 30+ values for Favorite Sport and 30+ for Favorite Color (and most sport-color pairings have at least one associated name, often as many as 20 names), so this would be very tedious to do manually, especially if I need to add or remove rows of data later.

http://www.mrexcel.com/forum/excel-questions/296309-pivot-table-show-detail-work-around.html

http://www.mrexcel.com/forum/excel-questions/772447-macro-insert-comments-based-cell-values.html

Any advice would be very appreciated. I’m happy to provide additional detail if anything’s unclear about what I’m trying to do. I'm using Excel 2016 for Windows.

 

[MarkPhil]

Sorry, forgot to provide context for the links I posted. They are both threads that seem related to a piece of what I'm trying to do, but they describe somewhat different situations and I'm not sure how to combine those strategies (or if there's a totally different way to go about this...)