Summing non-numerical survey data in a pivot table

Question

A project I am working on has me stumped. My question is I would like to put this data into a pivot table that I can sort by state in order to show the total number of each response response (a,b,c,ab,ac,bc) option to each question for the distinct states? Thanks.

State   Zip Code    Question 1  Question 2  Question 3
AL          35051   bc          b           c
AL          36853   b           ac          b
AL          35403   a           b           a
AK          99780   b           c           c
AK          99509   a           c           a
AK          99504   a           a           c

Do you still want them broken down by question, or somehow aggregated across questions - which seems weird but I'm asking anyways :) . — Doug Glancy, May 16 '14 at 17:13
So there is no way to consolidate all the data into one Pivot Table? — user3645441, May 16 '14 at 17:37
There is, but you need to reformat your data, per my answer. — Doug Glancy, May 16 '14 at 17:45
@pnuts, I can definitely understand wanting to maintain just one. It saves a lot of time, is less complicated for others to use, etc. — Doug Glancy, May 16 '14 at 17:59

score 1 · Answer 1 · edited May 23 '17 at 11:57

You can do this easily if you normalize your data. Then it's just a matter of counting answers. This picture should explain:

enter image description here

Short of that, pnuts solution is the best.

If you want a way to normalize, I've got code at https://stackoverflow.com/a/10922351/293078. If that's too much, there's other more automated solutions out there, like http://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/.

And here's a video of a different method from Contextures: https://www.youtube.com/watch?v=xmqTN0X-AgY.

Summing non-numerical survey data in a pivot table

1 Answers1