15

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.

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

4 Answers 4

24

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!

0
2
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.
1

Finally I found the exact solution for this request at this video:

Concatenate Unique Text Values in an Excel Pivot Table

I followed the steps and was able successfully to combine/merge the rows in pivot table of similar group or sub-group into CSV in one single column.

0

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .