Excel How to transform data from wide to long based on multiple columns

Question

I am a student learning more advanced Excel and I was curious if Excel had some built in features to handle pivoting more complex data. Or would it need to be scripted from scratch?

Say I have table like this:

ID	Name_1	Name_2	Name_3
100	Victor	Nina	Angela
200	Tim	John	Mike

It is easy to get this table with some PowerQuary: Dynamic restructure (wide -> long) in Excel

ID	Name
100	Victor
100	Nina
100	Angela
200	Tim
200	John
200	Mike

My Question

What if I have data like this with multiple different columns

ID	Name_1	Gender_1	Name_2	Gender_2	Name_3	Gender_3
100	Victor	Male	Nina	Female	Angela	Female
200	Tim	Male	John	Male	Mike	Male

I am having trouble finding ways to get to these table ->

ID	Name	Gender
100	Victor	Male
100	Nina	Female
100	Angela	Female
200	Tim	Male
200	John	Male
200	Mike	Male

I know I can do this in R with the pivot_longer function but am still getting familiar with excel do something like this.

I ask because I am working with some Adobe Fillable forms outputted as csv and this is how the the data is structured. I am hoping to have some direction in solving the problem. Thank you!

This is not a script writing service. Please show us code you have written so far. — music2myear, Apr 29 '22 at 03:40
@music2myyear will do! I am still very new to VBA (more used to R and Python) so I was hoping for some direction in the matter. I thought while I do my own research I would post this question since this small example might be of benefit to the community. My actual task that I base this question on is quite a bit more complicated. Sorry if I came off as wanting the work to be done for me. — Victor Feagins, Apr 29 '22 at 04:03

score 3 · Accepted Answer · answered Apr 29 '22 at 10:41

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 or From within sheet
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 the code comments, and also the Applied Steps window, to better understand the algorithm and steps

M Code

let

//Get data => change table name in next line to your actual table name
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

//Set the column data types=> ID=integer; other columns =>text
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {"ID", Int64.Type} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, type text})),

//Unpivot all except the ID column
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),

//Split the Attribute column on the underscore to have lists of identical attributes
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", 
        Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute"}),

//Group by ID
//  Then aggregate to create list of records where each record consists of each pair of values
//   effectively Pivoting with no aggregation
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"ID"}, {
        {"pivot", (t)=>let 
           recs = List.Generate(
               ()=> [r=Record.FromList({t[Value]{0},t[Value]{1}},{t[Attribute]{0},t[Attribute]{1}}),idx=0],
               each [idx]<Table.RowCount(t),
               each [r=Record.FromList({t[Value]{[idx]+2},t[Value]{[idx]+3}},{t[Attribute]{[idx]+2},t[Attribute]{[idx]+3}}),
                        idx=[idx]+2],
               each [r])
        in recs, type list}
       }),

//expand the list of records to new rows
    #"Expanded pivot" = Table.ExpandListColumn(#"Grouped Rows", "pivot"),

//expand the records to new columns
    #"Expanded pivot1" = Table.ExpandRecordColumn(#"Expanded pivot", "pivot", {"Name", "Gender"}, {"Name", "Gender"}),

//set the data types
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded pivot1",{{"Name", type text}, {"Gender", type text}})
in
    #"Changed Type1"

score 2 · Answer 2 · answered Apr 29 '22 at 16:22

Another option in formula solution for Excel 2019 and up.

In I2 array formula ("Ctrl+Shift+Enter") copied across to K2 and all copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF(MOD(COLUMN($B$2:$G$3),2)=0,$A$2:$A$3&"</b><b>"&$B$2:$G$3,"")&IF(MOD(COLUMN($B$2:$G$3),2)=1,$B$2:$G$3,""))&"</b></a>","//b["&ROW($A1)*3+COLUMN(A$1)-3&"]"),"")

Excel How to transform data from wide to long based on multiple columns

My Question

2 Answers2