Need help configuring this data

[slw_charleston]

Hi Everyone,

I'm working with a data set that looks like this (question stem is in first cell followed by answers that students picked. I'd like to move the question into its own column, repeat it for each answer and create new headers (see below for example).  I have several of these...so once I get it into three columns I have about 50 of these to arrange and then I'd like to stack them upon each other vertically.  Currently they are in columns moving across horizontally (each question on the exam has two columns like shown below listed horizontally). I want them to be arranged vertically in three columns and stacked.  I also have student names that correspond to each answer and will need to repeat them down the stacks.  In the end, each question will be repeated for each answer and score (so it ends up being 1000s of lines long). And each answer and score will have a student name attached which repeats for each question. Really need some help as it takes me forever to do in Excel and my arm hurts. :)

I'd like it to look like this:  Student names are to the left and I will need to repeat them for each question that I have.  There are a lot of questions (over 100 columns to get rearranged into a vertical stack).

[Qiu]

@slw_charleston 
Can you kindly share a input and output sample file?
I think it can be done with the Multi-Row Formula tool.

[Adrian_T]

Hey @slw_charleston 

You can achieve your target format using the Transpose Tool, grouped by Student Name. 

Here is a tool mastery article which will guide you to configure this: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Transpose/ta-p/89741

[binuacs]

@slw_charleston  as @Qiu mentioned this can be done using the multi-row formula, 

[slw_charleston]

Here's the full data set (on the RAW tab) and the reconfigured data for the first three questions (DESIRED tab)...as you can see there are a bunch more to do. Would love to automate the task.

[Adrian_T · Accepted solution]

Hey @slw_charleston 

This should do the trick, sample workflow attached. You are looking at using both the Transpose Tool and Multi-Row Formula Tools.

1. Transpose your data using the Transpose Tool, grouped by name, major and GPA

2. Use the Multi-Row Formula Tool to append the scores to a new column

3. You can add a Sort Tool depending on the order in which you want to sort your data.

[slw_charleston]

Thank you very much for your help.  You saved me a boat-load of time (and probably my wrist). :)