Un-flatten Survey Data

N

njfulton

New Member
Joined
Jul 23, 2019
Messages
4
I have survey data that requests responses from parents about the number of kids they would like registered for an activity.

[TABLE="width: 700"]
<tbody>[TR]
[TD]Parent Name[/TD]
[TD]Child 1 Name[/TD]
[TD]Child 1 T-Shirt Size[/TD]
[TD]Child 1 Birthday [/TD]
[TD]Child 2 Name[/TD]
[TD]Child 2 T-Shirt Size[/TD]
[TD]Child 2 Birthday[/TD]
[TD]Child 3 Name[/TD]
[TD]Child 3 T-Shirt Size[/TD]
[TD]Child 3 Birthday[/TD]
[TD]Child 4 Name[/TD]
[TD]Child 4 T-Shirt Size[/TD]
[TD]Child 4 Birthday[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]James Smith[/TD]
[TD]YS[/TD]
[TD]41143[/TD]
[TD]Jane Smith[/TD]
[TD]4T[/TD]
[TD]43556[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Jason Doe[/TD]
[TD]YS[/TD]
[TD]41143[/TD]
[TD]Sarah Doe[/TD]
[TD]4T[/TD]
[TD]43556[/TD]
[TD]Sam Doe[/TD]
[TD]2T[/TD]
[TD]43556[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 700"]
<tbody>[TR]
[TD]Parent Name[/TD]
[TD]Child Name[/TD]
[TD]Child T-Shirt Size[/TD]
[TD]Child Birthday[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]James Smith[/TD]
[TD]YS[/TD]
[TD]41143[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Jane Smith[/TD]
[TD]4T[/TD]
[TD]43556[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Jason Doe[/TD]
[TD]YS[/TD]
[TD]41143[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Sarah Doe [/TD]
[TD]4T[/TD]
[TD]43556[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Sam Doe [/TD]
[TD]2T[/TD]
[TD]43556[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for your help!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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.

Code: 
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
 
Last edited:
Upvote 0
with PowerQuery aka Get&Transform:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Parent Name[/td][td=bgcolor:#70AD47]Child Name[/td][td=bgcolor:#70AD47]Size[/td][td=bgcolor:#70AD47]DOB[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]John Smith[/td][td=bgcolor:#E2EFDA]James Smith[/td][td=bgcolor:#E2EFDA]YS[/td][td=bgcolor:#E2EFDA]
22/08/2012​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]John Smith[/td][td]Jane Smith[/td][td]4T[/td][td]
01/04/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Jane Doe[/td][td=bgcolor:#E2EFDA]Jason Doe[/td][td=bgcolor:#E2EFDA]YS[/td][td=bgcolor:#E2EFDA]
22/08/2012​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jane Doe[/td][td]Sarah Doe[/td][td]4T[/td][td]
01/04/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Jane Doe[/td][td=bgcolor:#E2EFDA]Sam Doe[/td][td=bgcolor:#E2EFDA]2T[/td][td=bgcolor:#E2EFDA]
01/04/2019​
[/td][/tr]
[/table]

Code: 
[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]
 
Upvote 0
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:
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:
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:
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"
 
Upvote 0
@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
 
Upvote 0
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.
 
Upvote 0
njfulton said:
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.
Click to expand...
Cheers. Glad to contribute to your options. Thanks for letting us know. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Index Match with multiple results with a twist
Replies
5
Views
660
Fluff
Fluff
W
  • Question Question
Count Unique Values in a list based on criteria
Replies
7
Views
928
Will85
W
W
  • Question Question
Formula Required to Group Family Members, where ID matches
2
Replies
13
Views
2K
jtakw
jtakw
W
  • Question Question
IF, OR, Index, Match, Multiple Criteria
2
Replies
15
Views
2K
edromny
E
N
  • Question Question
100K plus lines of data, DOUBLE VLOOKUP help required.
Replies
5
Views
688
offthelip
O

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top