counting strings in mulitple columns in a pivot

[moob]

I am having an excel problem ...

I have a data set that looks similar to the data below. My issue is there are three category columns. I need to sort count totals for each of the categories using a pivot so I can filter on the state, group or any combination of those.

I would like it to look similar to this:
row labels, a, b
red, 5, 4
yellow, 2, 3
blue, 1

or

row labels, red, black, yellow
Hawaii, 5, 4, 3
Florida, 3, 3, 5
Delaware, 5,1,3

Any advice would be appreciated.

Thanks



ID,State,Group,Category 1 ,Category 2,Category 3
8324383647,Hawaii,a,blue,yellow,red
2953955713,Delaware,b,black,yellow,
4507672544,Guam,a,blue,green,black
8413529531,Florida,a,blue,red,black
9106260590,Colorado,b,green,blue,
3734034084,Connecticut,b,yellow,red,
595724048,Florida,b,black,,
6952839700,Colorado,a,green,black,
8091999421,Delaware,a,red,blue,green
5307579677,Arizona,a,blue,green,black
2530425473,Arizona,b,red,black,
8746887928,Delaware,a,green,red,
6346139276,Illinois,a,green,,
6748759844,Florida,b,black,green,blue
6442505018,Florida,b,yellow,green,blue
9526777825,Iowa,b,red,black,
5024825750,Idaho,a,red,,
1430512889,Kansas,b,yellow,red,
8807559301,Delaware,b,yellow,black,blue
9095303168,Delaware,b,green,,
4303027194,Illinois,a,green,blue,black
3160985952,Connecticut,a,blue,yellow,blue
577493020,Georgia,a,black,red,
2713613493,Hawaii,b,blue,black,red
4107039177,Guam,a,blue,red,yellow
5464451508,Kansas,b,blue,green,red

[DonkeyOte]

Realistically for this data to source a Pivot you will need to first transpose the set such that each Category is a separate transaction.

There are a few approaches to this - VBA etc - but if you want to avoid code you can using a Multi Consolidation Pivot Table to do this, see:

http://datapigtechnologies.com/blog/...-a-pivottable/

http://datapigtechnologies.com/flash...osetables.html

for more info. on this technique

[moob]

Thanks, yeah that should work. I was hoping to not have to duplicate information, but I guess that is the way it needs to be done.

Thanks for your help