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

I have two columns of data:


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


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.

  • You need an SQL query to do that easily. Can you export to a database of some description? Feb 24 '11 at 0:30

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!

C1  C2  C3          C4
a   1   1   
a   2   2,1 
a   3   3,2,1   
a   5   5,3,2,1 
a   3   3,5,3,2,1   New
b   11  11  
b   13  13,11   
b   11  11,13,11    
b   14  14,11,13,11 New
c   22  22  
c   24  24,22       New
f   25  25  
f   11  11,25   
f   10  10,11,25    New
  1. In col3 (means C3) use formula =IF(A2=A1,B2&","&C1,B2) starting cell C2 onward and drag it down.
  2. in C4 (means column4) use formula =IF(A2=A3,"","changed") starting cell D2 onwards and drag it down.
  3. Filter on New on C4 (means col4) and you will get ur desired result.

Add column C as counter*: =COUNTIF(A$2:A$528, A2)
Add D as increment*=IF(A2=A1,D1+1, 1)
Add E to concatenate*: =IF(A1=A2,E1 & ", " & B2 , B2)
Add F to keep only last concat : =IF(AND(C2=D2,E4<>""), E4 ,"")
add on cell 2 and pull down (or double click on right corner) to apply formula for all cells in the column

copy and paste as values in another sheet, sort descending by F, delete the rest

  • too much to do... F106dart's solution is more elegant.
    – ihightower
    Apr 3 '15 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.