Summarizing survey data

[YeahMan]

Hi Community,

I'm trying to summarize the attached survey data. I have questions 1a - 1m all of which have the same set of answers to choose from:

All of my courses

Most of my courses

Some of my courses (ie., about half)

Few of my courses

None of my courses

Not applicable

blanks

I would like to count the responses, remove Not applicable & blanks, and get the output close to the following format:

Q1a_All of my courses	Q1a_Most of my courses	Q1a_Some of my courses (ie., about half)	Q1a_Few of my courses	Q1b_All of my courses	Q1b_Most of my courses	Q1b_Some of my courses (ie., about half)	Q1b_Few of my courses	…
6	12	33	5	10	14	11	20

I'm pretty close using the following workflow (attached):

But was wondering how to:

• get the column names in the format I want (from above).
• get around having to repeat the above workflow for each of 1b - 1m (there are another couple of sets of questions in a similar format but with different answer choices, so this could get ugly fast)
• I did try using the 'Field Info' tool to just grab all the Q1's but wasn't sure how to glue it all back together again.

Thanks,

--sue

[binuacs]

@YeahMan  are you looking for something like below

[ncrlelia · Accepted solution]

Hi @YeahMan ,

You can Transpose all rows and columns at the same time. To ensure we have the required names and order of the columns in the final output, I suggest to create a mapping table that have all the possible column names. Attached workflow for your reference.

Hope this helps.

Thank you.

Best Regards,

Lelia 

[grazitti_sapna]

@YeahMan, try this.

Thanks!

[YeahMan]

Wow, this community is amazing. Thank you all so much for the help!

--sue

[YeahMan]

Thank you Lelia! This is a thing of beauty. I still have 8 questions to go with different response options and then group by the appropriate input (college, school, program and MCU), so there is still a fair bit of work to be done. Oh yeah, I still have to create the final Excel report with graphs! But this solution helps so much. Very much appreciated.

--sue