How can I concatenate values in an Excel pivot, as opposed to summing or counting

Question

Say I have the following data:

Col1   Col2
------------
Bob    Green
Bob    Green
Bob    Green
Chris  Blue
Chris  Blue
Chris  Red
Dave   Blue
Dave   Green
Dave   Red

A default Pivot Table in Excel would yield:

Col1   Col2
------------
Bob    Green
Chris  Blue
       Red
Dave   Blue
       Green
       Red

Is there any way to concatenate on Col2 (specifically, with separators), so I end up with this?:

Col1   Col2
------------
Bob    Green
Chris  Blue,Red
Dave   Blue,Green,Red

Answer 1

What you want is not possible from a PivotTable. However, if Bob is in A2 a formula in C2 like:

=IF(A1=A2,C1&", "&B2,B2)  

and another in D2 of:

=A2=A3  

both copied down to suit, may serve if you "fill in the gaps" (either in the PT or with Go To Special, Blanks, =, Up, Ctrl+Enter) then select all, Copy, Paste Special, Values over the top and filter to delete rows showing TRUE.

TEXTJOIN is now available on some versions.