Multiple values in one cell PIVOT table

[andros2309]

Dear helpers ,

This is my situation :

Column A Column B

Supplier 1 Product A, Product B, Product C
Supplier 2 Product A, Product C
Supplier 3 Product C, Product D
Supplier 4 Product B, Product C

I would like to create a PIVOT with the following result:

Product A : 2
Product B : 2
Product C : 4
Product D : 1

I can only seem to have results such as : Product A, Product B, Product C counting as one single value

Thank you in advance for any help

[Andy Pope]

you need to change the layout of your data

Supplier Product
Supplier 1 Product A
Supplier 1 Product B
Supplier 1 Product C
Supplier 2 Product A
Supplier 2 Product C
Supplier 3 Product C
Supplier 3 Product D
Supplier 4 Product B
Supplier 4 Product C

[andros2309]

Thanks but i was hoping to be able to keep the source data as it is... ?

[Andy Pope]

Pivot tables don't work that way. So you either manipulate the data so it works or have the results in the current format.