Excel – pivot values in one cell (as comma-separated value)

microsoft excel

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.

Best Answer

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!

Related Solutions

Excel – How to change Excel Pivot table “Report Filter”s values cell formatting

These numbers look like typical "sequential serial number" format of the Date function, meaning the number of the day.

The cell that displays "MONTH yyyy" is formatted as a date, probably a custom date format since this is not a standard display format. Or maybe you have used the Group By option of the Pivot Table and have chosen "Group by Month".

If you wish to change the display format of a cell, see Microsoft's Format a date the way you want.

We need to know more about your datasheets to give better advice.

Excel – Cannot sort Excel Pivot Table by two or more columns

Use a calculated field

The best way I've found to achieve this is to add a calculated field to the table that produces the correct sort order, and then to hide that column.

The sort order field needs to work by scaling each sort column up or down so that no column's bit of the output sort value overlaps with any other.

So I scale OrderCount up by a big number, and total Value down by a big number:

= (OrderCount*1000) + (Value/1000)

For Jemaine's figures above, this will produce a value of 3000.18. Bret would be 3000.07, Murray/Brian 1000.09 etc - the correct order.

If I wanted to add a third sorting condition I'd have to scale that down by an even bigger number:

= (OrderCount*1000) + (Value/1000) + (3rdCondition/1000000)

Adding the Calculated field

To actually implement this in the pivot table, I need to add a column to my data table so that the calculated field can count orders. I've simply added an OrderCount column and set the value to 1 for every row:

Now we can add the calculated field. Select this option from the ribbon and enter the formula:

My pivot table now looks like this, sorted correctly.

Tidying Up

This SortOrder column might confuse other users, so I can just hide the whole column.

However, I often prefer not to hide columns (personal preference) so to make this column unobtrusive I can rename the column to (ie a blank space), and format the number column for the SortOrder values to a custom format of "";"";"". If Excel autoadjusts column widths this makes the column barely noticeable.

Best Answer

Related Solutions

Excel – How to change Excel Pivot table “Report Filter”s values cell formatting

Excel – Cannot sort Excel Pivot Table by two or more columns

Related Question