How to make a pivot table frm interview results which contain more than 1 answer in 1 cel?

Dear all,

Target
I have made a table which includes a lot of interview/questionnaire data. Of these data I have made many different pivot tables with their own charts and one universal filter which is linked to them all. Everything works properly, but I only have one thing I cannot solve..

A few answers on questions can contain more than one answer. For example:

Question: What kind of music style do you prefer?
Answers:
1. pop, rock, country
2. pop, rock
3. country, classical, pop
4. pop
5. classical, rock
And so on.

Now, I would like to have a pivot table which shows how many people listen to pop, how many people listen to rock, how many people listen to classical, and so on..

Problem and Question
If I make a pivot table from this data in this status, the pivot table sees everything as one separate answer (so, it sees "pop, rock, country" as 1 answer, "pop, rock" as 1 answer, etc). This means, it counts not the separate music styles, but only counts the separate answers. Has someone the solution for this problem?

The thing I already tried
I tried to make extra rows in my original data table that show the answers separately. Every person got a 0 if he doesn't like the music style and every person got a 1 if he does like the music style. To give an example:

	Pop	Classical	Rock	Country
Person 1	1	0	1	1
Person 2	1	0	1	0
Person 3	1	1	0	1

In this way it can easily count how many people like what kind of music style. The only problem is, I still cannot create one pivot table which includes all these data. The only thing I can do with this, is to make 4 different pivot tables with their own separate music style. This means, I will also get 4 different charts which all are showing one bar only. If it is possible to combine these 4 pivot tables into 1 pivot table or to combine the 4 pivot charts into 1 pivot chart, it also will give me the solution. Nevertheless, I have read so many forums, articles, etc. but I can't find the solution of this anywhere!

I hope I clarified it well.
I really really hope someone can help me!

Thank you a lot!

Wendy

Hi Chris,

Thank you for your effort.
You mean something like this, right? http://excelhints.com/2010/01/29/usi...separate-data/

I think this doesn't work, because it will cause the same problem as with the other thing I already tried. It's the same kind of idea. Instead of the "0" and "1", I will get the words of the music styles in the rows. If I make a pivot table with these data, I get the same as with the 0s and 1s... (4 different kind of tables). If I place them all in one table, they will be grouped and clusterend under another music style (So Pop is clusterd in Rock and Country is clustered in Pop, etc.). I don't want them to be clustered in one another, because they all need to have the same "level".

Do you understand what I mean?

Wendy

Thank you Andy Pope! I'm afraid though, that also doesnt work. It would work if that's the only data I have, but I have many many data. If a cel in a row is empty, the pivot tables cannot "read" the rows and thereby skip the data of the total row. If I copy the other data and place them into the rows to fill them, the other pivot tables get double information.

But, nevertheless, I have found the solution by trying and trying!! It's very stupid though, because I was thinking too much difficult.
The only thing I had to do was placing the catgories (with the "0" and "1" into the pivot "SUM"-field (Im sorry, I dont know the official name, because Im using the Japanese version which I cannot read). I was trying to make tables with data in the axises, but I found out I need to leave them empty.

Thank you a lot! Maybe someone else can use this information in the future too!

Wendy