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

For Alteryx Community.xlsx

Q1a.yxmd

ncrlelia

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 

Workflow.yxmd