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:

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.

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 5 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
1  
This is magic - thanks! –  Chris Feb 28 '11 at 11:56

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 ,"")
Note
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

Your Answer

 
discard

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.