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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]ID#[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Favorite Sport[/TD]
[TD="align: center"]Favorite Color[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Angela[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]Blue[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Bill[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]Blue[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Chris[/TD]
[TD="align: center"]Basketball[/TD]
[TD="align: center"]Red[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Denise[/TD]
[TD="align: center"]Basketball[/TD]
[TD="align: center"]Red[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Eric[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]Red[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Frances[/TD]
[TD="align: center"]Hockey[/TD]
[TD="align: center"]Red[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Red[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Basketball[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Hockey[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

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...)