Un-flatten Survey Data

[njfulton]

I have survey data that requests responses from parents about the number of kids they would like registered for an activity.

Parent Name

Child 1 Name

Child 1 T-Shirt Size

Child 1 Birthday

Child 2 Name

Child 2 T-Shirt Size

Child 2 Birthday

Child 3 Name

Child 3 T-Shirt Size

Child 3 Birthday

Child 4 Name

Child 4 T-Shirt Size

Child 4 Birthday

John Smith

James Smith

YS

41143

Jane Smith

4T

43556

Jane Doe

Jason Doe

YS

41143

Sarah Doe

4T

43556

Sam Doe

2T

43556

<tbody>
</tbody>

I would like to transform the data so that each child name is on a new line, but the parent name remains tied to the child in the first column.

Parent Name	Child Name	Child T-Shirt Size	Child Birthday
John Smith	James Smith	YS	41143
John Smith	Jane Smith	4T	43556
Jane Doe	Jason Doe	YS	41143
Jane Doe	Sarah Doe	4T	43556
Jane Doe	Sam Doe	2T	43556

<tbody>
</tbody>

Thank you for your help!

 

[Peter_SSs]

Welcome to the MrExcel board!

Give this macro a try in a copy of your workbook.
It should put the results to the right of the original data which I have assumed starts in cell A1 of the active sheet.

Sub Unflatten()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, Cells(1, Columns.Count).End(xlToLeft).Column)
    a = .Value
    uba2 = UBound(a, 2)
    ReDim b(1 To .SpecialCells(xlConstants).Count / 4, 1 To 4)
    For i = 2 To UBound(a)
      For j = 2 To uba2 Step 3
        If Len(a(i, j)) > 0 Then
          k = k + 1
          b(k, 1) = a(i, 1): b(k, 2) = a(i, j): b(k, 3) = a(i, j + 1): b(k, 4) = a(i, j + 2)
        End If
      Next j
    Next i
    With .Offset(, .Columns.Count + 1).Resize(, 4)
      .Rows(2).Resize(k).Value = b
      .Rows(1).Value = Array("Parent Name", "Child Name", "Child T-Shirt Size", "Child Birthday")
      .EntireColumn.AutoFit
    End With
  End With
End Sub

 

[sandy666]

with PowerQuery aka Get&Transform:

Parent Name	Child Name	Size	DOB
John Smith	James Smith	YS	22/08/2012​
John Smith	Jane Smith	4T	01/04/2019​
Jane Doe	Jason Doe	YS	22/08/2012​
Jane Doe	Sarah Doe	4T	01/04/2019​
Jane Doe	Sam Doe	2T	01/04/2019​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Parent Name", type text}, {"Child 1 Name", type text}, {"Child 1 T-Shirt Size", type text}, {"Child 1 Birthday", type date}, {"Child 2 Name", type text}, {"Child 2 T-Shirt Size", type text}, {"Child 2 Birthday", type date}, {"Child 3 Name", type text}, {"Child 3 T-Shirt Size", type text}, {"Child 3 Birthday", type date}, {"Child 4 Name", type any}, {"Child 4 T-Shirt Size", type any}, {"Child 4 Birthday", type date}}),
    Merge1 = Table.CombineColumns(Table.TransformColumnTypes(Type, {{"Child 1 Birthday", type text}}, "en-GB"),{"Child 1 Name", "Child 1 T-Shirt Size", "Child 1 Birthday"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged"),
    Merge2 = Table.CombineColumns(Table.TransformColumnTypes(Merge1, {{"Child 2 Birthday", type text}}, "en-GB"),{"Child 2 Name", "Child 2 T-Shirt Size", "Child 2 Birthday"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged.1"),
    Merge3 = Table.CombineColumns(Table.TransformColumnTypes(Merge2, {{"Child 3 Birthday", type text}}, "en-GB"),{"Child 3 Name", "Child 3 T-Shirt Size", "Child 3 Birthday"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged.2"),
    Merge4 = Table.CombineColumns(Table.TransformColumnTypes(Merge3, {{"Child 4 Name", type text}, {"Child 4 T-Shirt Size", type text}, {"Child 4 Birthday", type text}}, "en-GB"),{"Child 4 Name", "Child 4 T-Shirt Size", "Child 4 Birthday"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged.3"),
    UOC = Table.UnpivotOtherColumns(Merge4, {"Parent Name"}, "Attribute", "Value"),
    RC = Table.RemoveColumns(UOC,{"Attribute"}),
    Filter = Table.SelectRows(RC, each ([Value] <> "==")),
    Split = Table.SplitColumn(Filter, "Value", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    Type1 = Table.TransformColumnTypes(Split,{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type date}}),
    Rename = Table.RenameColumns(Type1,{{"Value.1", "Child Name"}, {"Value.2", "Size"}, {"Value.3", "DOB"}})
in
    Rename[/SIZE]

 

[lrobbo314]

I was hoping to see you chime in on this one Sandy. This was more difficult than I imagined. Here's what I did.

Add table to PQ.
Duplicate Table.
Merge Queries.

The only benefit to the code below as far as I can tell is, if they were to add columns to the original table, this would pick that up. Seems like you have to do all of that manually with your solution. Either way, it was fun figuring this one out and seeing other techniques to get it solved.

Table1 Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Parent Name"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Middle([Attribute],8)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"Count", each _, type table [Parent Name=text, Value=anynonnull, Custom=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Column([Count],"Value")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter",{"Count"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"T-Shirt Size", type text}, {"Birthday", Int64.Type}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Birthday", type date}})
in
    #"Changed Type2"

Table2 Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent Name", type text}, {"Child 1 Name", type text}, {"Child 1 T-Shirt Size", type text}, {"Child 1 Birthday", Int64.Type}, {"Child 2 Name", type text}, {"Child 2 T-Shirt Size", type text}, {"Child 2 Birthday", Int64.Type}, {"Child 3 Name", type text}, {"Child 3 T-Shirt Size", type text}, {"Child 3 Birthday", Int64.Type}, {"Child 4 Name", type any}, {"Child 4 T-Shirt Size", type any}, {"Child 4 Birthday", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Child 1 T-Shirt Size", "Child 1 Birthday", "Child 2 T-Shirt Size", "Child 2 Birthday", "Child 3 T-Shirt Size", "Child 3 Birthday", "Child 4 T-Shirt Size", "Child 4 Birthday"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Parent Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Merged Code:

let
    Source = Table.NestedJoin(Kids, {"Name"}, Parents, {"Value"}, "Parents", JoinKind.LeftOuter),
    #"Expanded Parents" = Table.ExpandTableColumn(Source, "Parents", {"Parent Name"}, {"Parents.Parent Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Parents",{"Parents.Parent Name", "Name", "T-Shirt Size", "Birthday"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Parents.Parent Name", "Parent"}})
in
    #"Renamed Columns"

 

[sandy666]

@lrobbo314
I did it to show it was possible via PQ and it was fun but M-code was not optimised for full automation.
I can't say that it was difficult, it all depends on your imagination and that's why I like the last line of your footer in posts

 

[njfulton]

This worked! Thank you so much!

 

[sandy666]

This worked! Thank you so much!

it would be nice to know which one ...

 

[njfulton]

Both the VBA & the PQ solutions worked. I ended up going with the VBA solution for this specific instance because I'm not as familiar with PQ, however this has me motivated to learn it more! Thanks again.

 

[sandy666]

You are welcome and thanks for the feedback

Have a nice day

 

[Peter_SSs]

Both the VBA & the PQ solutions worked. I ended up going with the VBA solution for this specific instance because I'm not as familiar with PQ, however this has me motivated to learn it more! Thanks again.

Cheers. Glad to contribute to your options. Thanks for letting us know.