Pivot Tables - where Cell 'Contains' text...

[jayne99]

Hello, can anyone please let me know if it is possible to create a pivot table but specifying a count where data within a column contains a specific string (similar to the option available when filtering)?

Sample scenario - a question within a survey allows the user to select their top 3 choices for their answer - the data returned would appear similar to 'answer2; answer5; answer7' in one cell - answers for each complete survey would therefore be random. We need to analyse the responses, and therefore somehow return a count of each individual answer selected (answer1, answer2, answer3, etc.), then by using pivot tables, cross check against a user's Industry, Country, etc.

I have checked Excel version 97, 2003 and 2007 but can't find a way to do this. Can anyone help??

Thanks Jayne

 

[andrewman]

You should use the Microsoft Query first.


Andrew Man

 

[Andrew Poulsom]

Welcome to the Board.

Say you have respondent names in column A and answers in column B. In row 1 starting at column C enter the possible answers, ie answer1, answer2, answer3, etc. In C2 enter:

=ISNUMBER(SEARCH(C$1,$B2))+0

and copy down and across. Now create a pivot table with respondent name as the row field and sum of each of the possible answers in the data field. If you click on the Data field and drag to the right the table will be easier to read.