I'm consolidating survey results and my input data is in the following format: SectionQuestion numberQuestionResponse11.01Placeholder 1Company 1 response - 1.0111.02Placeholder 2Company 1 response - 1.0211.03Placeholder 3Company 1 response - 1.0311.04Placeholder 4Company 1 response - 1.0411.05Placeholder 5Company 1 response - 1.0511.01Placeholder 1Company 2 response - 1.0111.02Placeholder 2Company 2 response - 1.0211.03Placeholder 3Company 2 response - 1.0311.04Placeholder 4Company 2 response - 1.0411.05Placeholder 5Company 2 response - 1.0511.01Placeholder 1Company 3 response - 1.0111.02Placeholder 2Company 3 response - 1.0211.03Placeholder 3Company 3 response - 1.0311.04Placeholder 4Company 3 response - 1.0411.05Placeholder 5Company 3 response - 1.05 I would like to get in the following output format: SectionQuestion numberQuestionResponse 1Response 2Response 311.01Placeholder 1Company 1 response - 1.01Company 2 response - 1.01Company 3 response - 1.0111.02Placeholder 2Company 1 response - 1.02Company 2 response - 1.02Company 3 response - 1.0211.03Placeholder 3Company 1 response - 1.03Company 2 response - 1.03Company 3 response - 1.0311.04Placeholder 4Company 1 response - 1.04Company 2 response - 1.04Company 3 response - 1.0411.05Placeholder 5Company 1 response - 1.05Company 2 response - 1.05Company 3 response - 1.05 (I don't care much about the header names as I can always update them later), but essentially I'm asking how I can keep the first 3 columns and then transpose the response column to be separate for each company's response. I know it's probaby either the transpose or cross tab tool, but I haven't used them in a while so I'm rusty and confused. Caveats: - Some of the company responses have commas within the response, so cross tab and parsing by commas won't work - In some cases, the first 3 columns have the same exact wording, which causes confusion because it looks like Alteryx combines them (see the "Details" row below) Example: SectionQuestion numberQuestion11.01Question 1a11.01Details11.01Question 1b11.01Details

Hi,@jgiacomazzi your require like other we answered post yesterday. ****************** As your data of Sample survey data2.xlsx

Transpose/Cross Tab one column

jgiacomazzi

I'm consolidating survey results and my input data is in the following format:

Section	Question number	Question	Response
1	1.01	Placeholder 1	Company 1 response - 1.01
1	1.02	Placeholder 2	Company 1 response - 1.02
1	1.03	Placeholder 3	Company 1 response - 1.03
1	1.04	Placeholder 4	Company 1 response - 1.04
1	1.05	Placeholder 5	Company 1 response - 1.05
1	1.01	Placeholder 1	Company 2 response - 1.01
1	1.02	Placeholder 2	Company 2 response - 1.02
1	1.03	Placeholder 3	Company 2 response - 1.03
1	1.04	Placeholder 4	Company 2 response - 1.04
1	1.05	Placeholder 5	Company 2 response - 1.05
1	1.01	Placeholder 1	Company 3 response - 1.01
1	1.02	Placeholder 2	Company 3 response - 1.02
1	1.03	Placeholder 3	Company 3 response - 1.03
1	1.04	Placeholder 4	Company 3 response - 1.04
1	1.05	Placeholder 5	Company 3 response - 1.05

I would like to get in the following output format:

Section	Question number	Question	Response 1	Response 2	Response 3
1	1.01	Placeholder 1	Company 1 response - 1.01	Company 2 response - 1.01	Company 3 response - 1.01
1	1.02	Placeholder 2	Company 1 response - 1.02	Company 2 response - 1.02	Company 3 response - 1.02
1	1.03	Placeholder 3	Company 1 response - 1.03	Company 2 response - 1.03	Company 3 response - 1.03
1	1.04	Placeholder 4	Company 1 response - 1.04	Company 2 response - 1.04	Company 3 response - 1.04
1	1.05	Placeholder 5	Company 1 response - 1.05	Company 2 response - 1.05	Company 3 response - 1.05

(I don't care much about the header names as I can always update them later), but essentially I'm asking how I can keep the first 3 columns and then transpose the response column to be separate for each company's response. I know it's probaby either the transpose or cross tab tool, but I haven't used them in a while so I'm rusty and confused.

Caveats:

- Some of the company responses have commas within the response, so cross tab and parsing by commas won't work

- In some cases, the first 3 columns have the same exact wording, which causes confusion because it looks like Alteryx combines them (see the "Details" row below)

Example:

Section	Question number	Question
1	1.01	Question 1a
1	1.01	Details
1	1.01	Question 1b
1	1.01	Details

Transformation

Accepted answers

All comments

DataNath

How does the below solution look? I just manually added a 'Response #' field but it'd be easy enough to do this dynamically (i.e. parse or assign a unique ID to each company) in your live workflow. I didn't quite understand your caveats so if this doesn't work then let me know, give a bit more context (perhaps with a screenshot or data snippet where I can recreate it) and I'll have a rethink!

Cross Tab on Question ID.yxmd

jgiacomazzi

Thank you so much! This would definitely work if I had the response # field. Can you show me how you would create it using parse or assigning a unique ID? The response itself likely wouldn't have "company 1", "company 2", etc. in it.

DataNath

Are you able to show a small sample of how responses look? Just so I can see how we might go about differentiating between different companies/groups of respondents. Even if you just mock up something close to the real data!

jgiacomazzi

Just attached sample of responses. Thank you for your help!

Sample survey data2.xlsx

DataNath

Awesome, thanks for that!

I've attached a workflow containing a solution below. Hopefully this is acceptable for what you're looking for as there is a slight 'Q num' modification in there to allow for differentiation when necessary.

This also assumes that the whole dataset follows this format!

Cross Tab on Question ID continued.yxmd

fharper

your sample data has no identifier for the responder, in a separate field or embedded in the response.

In surveys we often do not get to know who said what... but we often have responses that duplicate, 1,000 people surveyed and 400 prefer Red, 300 prefer Blue, 150 prefer Green and 150 prefer yellow...

To expand on the work by @DataNath a couple Summarization tools to get the count of a given response within the key structure or question and total responses to a question and run with it to show not only the unique or top N unique and their count or %Responded...etc.

I did do cleanup on the test data to show multiples of a response and to align question# to be unique per question text

For some reason I can't upload data or workflows so I am showing some screen shots...sorry

flying008

Hi,@jgiacomazzi

your require like other we answered post yesterday.

录制_2022_04_28_14_18_15_440.gif

******************

As your data of Sample survey data2.xlsx

录制_2022_04_28_14_29_41_818.gif

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335