EXCEL - How to group multiple rows ("children") into columns linked to same "parent"?

I'm trying to build a mail listing for our school parents, and I am not sure how to do the formula in Excel.

I have a long list of children (name and class) and for each of them their parent (name and email address)

It looks like this table List of children

I would like to transform it, based on the parent, for the mailing list. The unique ID is becoming the parent email address, and not anymore the name of the child.

Each parent on a row, with the children in additional columns. (If a parent has 4 children, I will need to have 4 times the 3 columns name/surname/class)

enter image description here

Thanks a lot for your help Thierry

Solution 1:

Here's one way of doing this using Power Query.

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

If this works, see my note for the last step about generating the final column name lists dynamically. This can be added if the basic algorithm works and you need the flexibility.

M Code

let

//Read in the data and set data types
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Child Name", type text}, {"Child Surname", type text}, {"Class", Int64.Type}, 
        {"Parent Name", type text}, {"Parent email address", type text}}),

//Unpivot columns other than the two Parent columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
        {"Parent email address", "Parent Name"}, "Attribute", "Value"),

//Group by the Parent Columns
//Then add an index colum to each sub-table
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Parent Name", "Parent email address"}, {
        {"idx", each  Table.AddIndexColumn(_,"idx"),Int64.Type}}),

//Add integer/divide by 3 column to each subtable to differentiate the different children
//Note that we set the data type to Text for later combining with the Attributes
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "int/div", 
        each Table.TransformColumnTypes(
            Table.AddColumn([idx], "Integer-Division", 
                each Number.IntegerDivide([idx], 3)),{"Integer-Division",Text.Type})),

//Combine Attribute and int/divide column to create unique names for each child/column
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "childNum", 
        each Table.CombineColumns([#"int/div"],
            {"Attribute","Integer-Division"},
                Combiner.CombineTextByDelimiter("."),"Attribute")),

//Remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"idx", "int/div"}),

//Pivot each sub-table
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "pivot subtable", each 
        let 
            //Remove columns we don't want when we pivot
            x=Table.RemoveColumns([childNum],{"Parent Name", "Parent email address", "idx"})
        in 
            Table.Pivot(x,x[Attribute],"Attribute","Value")),

//Remove unneeded columns and expand the pivot subtables
//May need to generate the list of expanded column names dynamically
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"childNum"}),
    #"Expanded pivot subtable" = Table.ExpandTableColumn(#"Removed Columns1", "pivot subtable", 
        {"Child Name.0", "Child Surname.0", "Class.0", "Child Name.1", "Child Surname.1", "Class.1", "Child Name.2", "Child Surname.2", "Class.2"}, 
        {"Child Name.0", "Child Surname.0", "Class.0", "Child Name.1", "Child Surname.1", "Class.1", "Child Name.2", "Child Surname.2", "Class.2"})
in
    #"Expanded pivot subtable"

Source

Results