Excel - pivot values in one cell (as comma-separated value)

Excel - pivot values in one cell (as comma-separated value)

I have two columns of data:

Supplier1|Product1
Supplier1|Product2
Supplier1|Product4
Supplier1|Product7
Supplier2|Product3
Supplier2|Product5

I want to 'pivot' around Supplier, and give the list of products in one single cell, comma-separated e.g.

Supplier1|Product1,Product2,Product4,Product7
Supplier2|Product3,Product5

There's about 1000 suppliers, and 0 < products <= 10.

My current workaround involves using pivot tables, saving as CSV etc and is very messy. A non-VBA solution would be amazing.

Answer

Here's a non-VBA, non-pivot table solution that only uses a couple of formulas.

1. 
   

   First, I used the "Text-to-columns" to split your data at that "pipe" delimiter (the vertical line) into 2 columns; a "Supplier" column and a "Product" column. Those go in columns A and B, respectively. (It appears in your post that they are combined in one column, so I first split them apart. You won't have to do this.)

   
   


2. 
   

   In column C, which I named as the "Concatenation" column, I used this formula, starting in cell C2 and copying all the way down: =IF(A2=A1,C1&", " & B2,A2&"|"&B2)

   
   


3. 
   

   In column D, which I named as "SupplierChangesAtNextLine?" I used this formula (starting in D2 and copying all the way down): =IF(A2=A3,"","Changed")

   
   


4. 
   

   You should now be able to filter on column D for only the "changed" values.

   
   

Good hunting!