pivot table?

[fairyqueen]

I have two coloums, one with gender, and one with subject choice. For the subject choice, there can be multiple answers (i.e. it could be maths, or it could be maths/english/geography), I want to get a comparassion of what subjects each gender choices (including if this is just one of their choices).

I think I'd do this in a pivot table, but I can't get it to work (am kinda new to pivot tables). I'm using excel 2007.

[DonkeyOte]

To use a Pivot Table you will need to split those entries with multiple subjects per cell into separate "transactions" - ie replicate the row for as many subjects as listed in the original with each "transaction" containing one of the listed subjects...

Using your example of maths/english/geography - this 1 transaction must become 3 (ie 3 rows) - math, english, geography each a separate row - the gender value of the original should of course be replicated in each row.

Once you've done the above you can then use a PT with say:

Gender Field as Column Label
Subject Field as Row Label
Subject Field used again but as the Data Field set to COUNT

this will give you a matrix output.

[fairyqueen]

Thanks for your response, but some respondents have over 20 subjects and there are 100,000's respondents - seperating them out would take weeks! Any chance anyone has a faster way?