Create comma delimited values ​​in Excel (using pivot table)?

Question

Is there a way to generate comma-delimited values ​​in Excel (optimally using a pivot table)? Consider the following data:

Object color
foo red
foo blue
bar red
bar blue
bar green
baz yellow

I would like to get the table as follows:

Object Count of Color Colors
foo 2 Red, Blue
bar 3 Red, Blue, Green
baz 1 Yellow

Is this possible in Excel? The data comes from an SQL query, so I can write a UDF with a recursive CTE to calculate, but that was for one special query, and I need a quick and dirty way to get denormalized data. In the end, it probably took longer to publish this than to write UDF, but ...

Answer 1

Here is a much simpler answer adapted from this superuser answer (HT to @yioann for specifying it and @ F106dart for the original):

Assuming the data is in columns A (category) and B (value):

• (C) "". , C2 : =IF(A2=A1, C1&","&B2, B2)
• (D) "Count". , D2, : =IF(A2=A1, D1+1, 1)
• (E) " ?". , E2, : =A2<>A3

B () E (Last Line?) TRUE.

:

   A         B      C                        D                    E
 +---------  -----  -----------------------  -------------------  ----------
1| Category  Value  Values                   Count                Last Line?
2| foo       Red    =IF(A2=A1,C1&","&B2,B2)  =IF(A2=A1, D1+1, 1)  =A2<>A3
3| foo       Blue   =IF(A3=A2,C2&","&B3,B3)  =IF(A3=A2, D2+1, 1)  =A3<>A2
etc.

Answer 2

, RDBMS, (MS SQL, MySQL ..).

Excel. , VBA, .

MS Access, Excel. Microsoft Access Excel . , , :

• Object, Color.

• , .

• MS Access.

• "" → "" (Ctrl O)

• Microsoft Excel

• .

• , .

• MyPivot.

• Visual Basic... → → Visual Basic (Alt F11)

• UDF:

  'Concat returns a comma-seperated list of items
  Public Function Concat (CategoryCol As String, _
                          ItemCol     As String) As String
      Static LastCategory As String
      Static ItemList     As String
  
      If CategoryCol      = LastCategory Then
          ItemList        = ItemList & ", " & ItemCol
      Else
          LastCategory    = CategoryCol
          ItemList        = ItemCol
      End If
      Concat = ItemList
  End Function
  

  
  

• VB

• "" .

• SQL View.

• SQL:

  SELECT 
      Object,
      COUNT (Color)                  AS [Count of Color],
      LAST (Concat (Object, Color))  AS [List 'O Colors]
  FROM
      MyPivot
  GROUP BY
      Object
  

  
  

• ( Datasheet).

• , 15 !;)
  :

  Object  Count of Color  List 'O Colors
  bar             3       Blue, Green, Red
  baz             1       Yellow
  foo             2       Blue, Red