Search code examples
excelpivot-tabledenormalizationcsv

Create comma-delimited values in Excel (using PivotTable)?

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

Object Color
foo    Red
foo    Blue
bar    Red
bar    Blue
bar    Green
baz    Yellow

I'd like to get a table like the following:

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

Is this possible in Excel? The data is coming from a SQL query, so I could write a UDF with a recursive CTE to calculate, but this was for a single ad-hoc query, and I wanted a quick-and-dirty way to get the denormalized data. In the end, it's probably taken longer to post this than to write the UDF, but...

Solution

  • Here's a much simpler answer, adapted from this superuser answer (HT to @yioann for pointing it out and @F106dart for the original):

    Assuming the data is in columns A (Category) and B (Value):

    1. Create a new column (C), and name it "Values". Use this formula, starting in cell C2 and copying all the way down: =IF(A2=A1, C1&","&B2, B2)
    2. Create a second new column (D), and name it "Count". Use this formula, starting in cell D2, and copying all the way down: =IF(A2=A1, D1+1, 1)
    3. Create a third new column (E), and name it "Last Line?". Use this fomula, starting in cell E2, and copying all of the way down: =A2<>A3

    You can now hide column B (Value) and filter column E (Last Line?) for only the TRUE values.

    In summary:

       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.