Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

up vote 10 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 at 18:05
wow!!! i salute you! –  ihightower Apr 3 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 at 10:05

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.