Group by column A value, transpose column B, column C row values for each grouped column A value

Question

This is in Excel 2016. I have a spreadsheet where each row represents a response to two questions "Qa" and "Qb" from a unique student. The spreadsheet columns are: "Section" (class section student is in), "Qa", and "Qb". Thus, if three students answered from the same class section, that section will be listed three times under "Section", with each unique students answers in the other columns.

I want to group by section and spread the answers to each question across a single row in separate columns. The number of columns to create will default to the section with the most unique responses

In this case, 10003 has the greatest number of responses, so I want to get the following end result.

I am at a loss with how to get this going. Something like grouping by the section but transposing the rows within that group?

Answer 1

As @ScottCraner pointed out, you can obtain your desired output using Power Query, available in Windows Excel 2010+ and Office 365 Excel

• Select some cell in your original table
• Data => Get&Transform => From Table/Range
• When the PQ UI opens, navigate to Home => Advanced Editor
• Make note of the Table Name in Line 2 of the code.
• Replace the existing code with the M-Code below
• Change the table name in line 2 of the pasted code to your "real" table name
• Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps

M Code

let

//Change table name in next row to actual table name in workbook
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],

//set data type
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", Int64.Type}, {"Qa", type text}, {"Qb", type text}}),

//Group by Section
//Add a 1-based Index column to each Group
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Section"}, {
        {"Row", each Table.AddIndexColumn(_,"Row",1,1)}}),

//Expand the grouped tables
    #"Expanded Row" = Table.ExpandTableColumn(#"Grouped Rows", "Row", {"Qa", "Qb", "Row"}, {"Qa", "Qb", "Row"}),

//Unpivot
//Merge Row and Attribute columns to create the q-number headers
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Row", {"Section", "Row"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", 
        {{"Row", type text}}, "en-US"),{"Attribute", "Row"},
        Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),

//Pivot on the Sorted Merged column with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Sort(List.Distinct(#"Merged Columns"[Merged])), "Merged", "Value")
in
    #"Pivoted Column"

Note that there are no empty columns (iow, there is no Qa-4)
If you really need an empty column, insert a step at the beginning replacing nulls with a blank

let

//Change table name in next row to actual table name in workbook
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],

//set data type
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", Int64.Type}, {"Qa", type text}, {"Qb", type text}}),

//if you really need a blank Qa column since you have four distinct Qb rows but only 3 Qa rows,
// then we insert the next line
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Qa", "Qb"}),

//Group by Section
//Add a 1-based Index column to each Group
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Section"}, {
        {"Row", each Table.AddIndexColumn(_,"Row",1,1)}}),

//Expand the grouped tables
    #"Expanded Row" = Table.ExpandTableColumn(#"Grouped Rows", "Row", {"Qa", "Qb", "Row"}, {"Qa", "Qb", "Row"}),

//Unpivot
//Merge Row and Attribute columns to create the q-number headers
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Row", {"Section", "Row"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", 
        {{"Row", type text}}, "en-US"),{"Attribute", "Row"},
        Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),

//Pivot on the Sorted Merged column with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Sort(List.Distinct(#"Merged Columns"[Merged])), "Merged", "Value")
in
    #"Pivoted Column"