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.
Here's a non-VBA, non-pivot table solution that only uses a couple of formulas.
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.)
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)
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")
You should now be able to filter on column D for only the "changed" values.
Good hunting!
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments