Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
You need an SQL query to do that easily. Can you export to a database of some description? – Rhys Gibson Feb 24 '11 at 0:30
up vote 14 down vote accepted

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!

share|improve this answer
This is magic - thanks! – Chris Feb 28 '11 at 11:56
Excellent. Thank you. – Danny Z Feb 28 '15 at 18:05
wow!!! i salute you! – ihightower Apr 3 '15 at 9:47

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

share|improve this answer
too much to do... F106dart's solution is more elegant. – ihightower Apr 3 '15 at 10:05
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.
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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