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.

wengyee wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html