Using Pivot Table to analyze multiple answers

[wengyee]

Hi Excel Experts,

I have data which are exported from Sharepoint List to excel in the
following format:

NAME BRANDS
Adam Sandler Marlboro;#Dunhill;#Salem
Tim Duncan Marlboro
Sally Sue Salem
Briana Matt Marlboro;#Dunhill

How can I use pivot table to display the following the following
results:

NAME BRANDS TOTAL
Marlboro Dunhill Salem
Adam Sandler 1 1 1 3
Tim Duncan 1 1
Sally Sue 1 1
Briana Matt 1 1 2
TOTAL 3 2 2 7

Any help or suggestions would be deeply appreciated. Thanks.

 

[Debra Dalgleish]

A pivot table won't help analyze the data in that format. If the brands
don't exceed the number of columns, you could create a table to count
the brands.

Assuming your data are in columns A and B, enter the brand names,
starting in C1, and going across row 1.
In cell C2, enter the formula:
=IF(ISNUMBER(SEARCH(C$1,$B2)),1,0)
Copy the formula across to the last column of data, and down to the last
row of data
For the totals, use the SUM function.