Restructuring survey data for pivot

[mkv517]

Hi,

I have survey data attached. Questions asked are in column A. Column B,C,D has information on question categories.

I want to keep A2-A30 as fixed columns to create pivots. Survey responses are Column E-G.

Can you help me create a workflow to transpose this data from wide to long structure. There are 100 more columns on survey response but i have kept only 3 to get idea on the workflow.

Output: A2-A30 as fixed column headers. So company ID, profile_position. etc... will be in columns. Total 30 columns and then 3 more columns : Question_Short for A31 onwards. Name = Company ID, Value: answers for each question

Survey response are numeric (rating on scale 1-6, text, single choice and multiple choice. I would need to convert string values to numeric values for statistical analysis. Will feed output in tableau

[aatalai]

@mkv517 take a look at this workflow and let me know how you get on

[mkv517]

Hi,

I need A2-A30 as fixed column headers. So company ID, profile_position. etc... will be in columns. Total 30 columns and then 2 more columns : Name and Value.

Name will be a duplicate company ID. Value will have numerical or text values for each answer.

[aatalai]

so something like this

[mkv517]

Columns to add : Question_Short for questions in A31 onwards.

Name = Company ID

Value: answers for each question

Question type

Category 2

Category 3