I need help transposing some 3rd-party Excel output that comes in this format below:

Background: This is survey data containing up to 10 questions and each employee MUST answer each question. So if data was collected from 10 employees for a survey of 3 questions, then the output file will contain (10x3) 30 rows of data

I need to rearrange this data for the "business side" and I realized that the desired output is beyond the scope of simply using TRANSPOSE() in Excel

Here is the final result that I've been asked to design

Basically, I need 1-row per employee with each question horizontally lined up and their responses.

Is this even possible? If so, any help would be greatly appreciated!

cheers

CodePudding user response：

This can also be accomplished using Power Query, available in Windows Excel 2010 and Excel 365 (Windows or Mac)

It is a simple Pivot with no aggregation, and can actually be done entirely from the UI.

I did change your table 1 as Jane seems to have two different answers to Q2 - I suspect the numerical answer is really for Q3

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

let

//read the data
//change the table name in next line to actual table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table34"]}[Content],

//set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Question", type text}, {"Response", type any}}),

//Pivot with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Question]), "Question", "Response")
in
    #"Pivoted Column"

CodePudding user response：

So this works, with an extra column in column D:

=A2&B2

So based on your data:

So, formula in cell B13:

=INDEX($C$2:$C$10,MATCH($A13&B$12,$D$2:$D$10,0))

The issue with your data is that Jane has two of Q2... I had to correct that.

For your list of names, you could copy and use remove duplicates or use unique().