Transpose colums to a row based on a keyword / number

C

corruptedlogic

New Member
Joined
Mar 8, 2018
Messages
12
Hey there, i need to do what seems easy, but i cannot wrap my head around it. I have a set of data that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]HH[/TD]
[TD]Name[/TD]
[TD] Account[/TD]
[TD]Fee[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jones[/TD]
[TD]IRA[/TD]
[TD]0.5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jones[/TD]
[TD]Individual[/TD]
[TD]0.8%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Smith[/TD]
[TD]Joint[/TD]
[TD]1.0%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Smith[/TD]
[TD]IRA[/TD]
[TD]0.5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Smith[/TD]
[TD]Trust[/TD]
[TD]0.9%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




I need to have some sort of function that will lookup every instance of HH then iterate through and pull every row and enter the data in column format like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]HH[/TD]
[TD]Name[/TD]
[TD]Account1[/TD]
[TD]Fee1[/TD]
[TD]Account2[/TD]
[TD]Fee2[/TD]
[TD]Account3[/TD]
[TD]Fee3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jones[/TD]
[TD]IRA[/TD]
[TD]0.5%[/TD]
[TD]Individual[/TD]
[TD]0.8%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Smith[/TD]
[TD]Joint[/TD]
[TD]1.0%[/TD]
[TD]IRA[/TD]
[TD]0.5%[/TD]
[TD]Trust[/TD]
[TD]0.9%[/TD]
[/TR]
</tbody>[/TABLE]



I have tried index/match to no avail so ended up scrapping that idea. Perhaps VBA is the answer but i have no clue where to start.
The end data set will have 1000+ entries that need to be examined and transposed. Any pointers or ideas greatly appreciated!

Thanks.
 
Sort by date Sort by votes
example:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom[/td][td=bgcolor:#70AD47]Custom.1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A1[/td][td][/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]A1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA2[/td][td][/td][td]AA[/td][td]AA2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]AAA1[/td][td][/td][td=bgcolor:#E2EFDA]AAA[/td][td=bgcolor:#E2EFDA]AAA1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B2[/td][td][/td][td]B[/td][td]B2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]B2[/td][td][/td][td=bgcolor:#E2EFDA]BB[/td][td=bgcolor:#E2EFDA]BB3,BB4[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BB3[/td][td][/td][td]CC[/td][td]CC1,CC2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BB4[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA2[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]CC1[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]CC2[/td][td][/td][td][/td][td][/td][/tr]
[/table]

with distinct values in column Custom.1

Code: 
[SIZE=1]let
    CharsToTrim = List.Transform({48..57}, each Character.FromNumber(_)),
    Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Trim([Column1],CharsToTrim)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each List.Distinct(Table.Column([Count],"Column1"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"[/SIZE]
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Ok, one last question on this, i promise! If i have other columns of data in the source (example, email address, phone etc), how would i add those into the query (there will be one data sample per HH)? I can figure out how to add them after the Source step, but then as soon as the Grouped Rows step happens, the column data that previously showed the email address now shows error. I have about 10 different columns to add but if you can teach me to fish, i can figure out the rest!

thank you again!!
 
Upvote 0
If I understand well you want something like column Custom.1 (post#11)

Code: 
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each List.Distinct(Table.Column([Count],"Column1"))),
you can use List.Distinct or not, it depends on your data and what you want to achieve.

would be fine to see (desensitized) example of source and expected result - here in table form or link to shared excel file (OneDrive, GoogleDrive or something like that)
 
Upvote 0
Ok, here is: PQ Data Sample

I used sometimes List.Distinct and sometimes not.
I used a comma as a separator (not a comma and spaces)
The result is from the UID column to the E-Mail Address column
You can try to get the next columns yourself

Source
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#4472C4]UID[/td][td=bgcolor:#4472C4]HH[/td][td=bgcolor:#4472C4]Notes[/td][td=bgcolor:#4472C4]Account[/td][td=bgcolor:#4472C4]Fee[/td][td=bgcolor:#4472C4]IMA Number[/td][td=bgcolor:#4472C4]First[/td][td=bgcolor:#4472C4]Middle[/td][td=bgcolor:#4472C4]Last[/td][td=bgcolor:#4472C4]Address[/td][td=bgcolor:#4472C4]City[/td][td=bgcolor:#4472C4]State[/td][td=bgcolor:#4472C4]Zip[/td][td=bgcolor:#4472C4]HOME Phone[/td][td=bgcolor:#4472C4]CELL Phone[/td][td=bgcolor:#4472C4]E-Mail Address[/td][td=bgcolor:#4472C4]Address2[/td][td=bgcolor:#4472C4]City3[/td][td=bgcolor:#4472C4]State4[/td][td=bgcolor:#4472C4]Zip5[/td][td=bgcolor:#4472C4]2nd First[/td][td=bgcolor:#4472C4]2nd Middle[/td][td=bgcolor:#4472C4]2nd Last[/td][td=bgcolor:#4472C4]2nd Address[/td][td=bgcolor:#4472C4]2nd City[/td][td=bgcolor:#4472C4]2nd State[/td][td=bgcolor:#4472C4]2nd Zip[/td][td=bgcolor:#4472C4]2nd HOME Phone[/td][td=bgcolor:#4472C4]2nd CELL Phone[/td][td=bgcolor:#4472C4]2nd E-Mail Address[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A1[/td][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]This is a note[/td][td=bgcolor:#DDEBF7]IRA[/td][td=bgcolor:#DDEBF7]
0.5​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]John[/td][td=bgcolor:#DDEBF7]Q[/td][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7]123 Main St[/td][td=bgcolor:#DDEBF7]Anytown[/td][td=bgcolor:#DDEBF7]ST[/td][td=bgcolor:#DDEBF7]
1234​
[/td][td=bgcolor:#DDEBF7]555-666-7777[/td][td=bgcolor:#DDEBF7]555-888-9999[/td][td=bgcolor:#DDEBF7]someone@nowhere.com[/td][td=bgcolor:#DDEBF7]789 1st Street[/td][td=bgcolor:#DDEBF7]Another Town[/td][td=bgcolor:#DDEBF7]ST[/td][td=bgcolor:#DDEBF7]
777888​
[/td][td=bgcolor:#DDEBF7]Jane[/td][td=bgcolor:#DDEBF7]T[/td][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]555-888-9977[/td][td=bgcolor:#DDEBF7]jane@email.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A2[/td][td]A[/td][td][/td][td]Joint[/td][td]
0.8​
[/td][td]
1​
[/td][td]John[/td][td]Q[/td][td]Smith[/td][td]124 Main St[/td][td]Anytown[/td][td]ST[/td][td]
1234​
[/td][td]555-666-7777[/td][td]555-888-9999[/td][td]someone@nowhere.com[/td][td]789 1st Street[/td][td]Another Town[/td][td]ST[/td][td]
777888​
[/td][td]Jane[/td][td]T[/td][td]Smith[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]555-888-9977[/td][td]jane@email.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]B1[/td][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7]Another Note[/td][td=bgcolor:#DDEBF7]IRA[/td][td=bgcolor:#DDEBF7]
0.45​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]N [/td][td=bgcolor:#DDEBF7]Other[/td][td=bgcolor:#DDEBF7]555 B Street[/td][td=bgcolor:#DDEBF7]Sometown[/td][td=bgcolor:#DDEBF7]ST[/td][td=bgcolor:#DDEBF7]
11223​
[/td][td=bgcolor:#DDEBF7]555-888-96969[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]email@email.com[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td]B2[/td][td]B[/td][td][/td][td]Trust[/td][td]
0.88​
[/td][td]
2​
[/td][td]A[/td][td]N [/td][td]Other[/td][td]555 B Street[/td][td]Sometown[/td][td]st[/td][td]
11223​
[/td][td]555-888-9669[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

partial result
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]UID[/td][td=bgcolor:#70AD47]HH[/td][td=bgcolor:#70AD47]Notes[/td][td=bgcolor:#70AD47]Account[/td][td=bgcolor:#70AD47]Fee[/td][td=bgcolor:#70AD47]IMA Number[/td][td=bgcolor:#70AD47]First[/td][td=bgcolor:#70AD47]Middle[/td][td=bgcolor:#70AD47]Last[/td][td=bgcolor:#70AD47]Address[/td][td=bgcolor:#70AD47]City[/td][td=bgcolor:#70AD47]State[/td][td=bgcolor:#70AD47]Zip[/td][td=bgcolor:#70AD47]HOME Phone[/td][td=bgcolor:#70AD47]CELL Phone[/td][td=bgcolor:#70AD47]E-Mail Address[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A1,A2[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]This is a note[/td][td=bgcolor:#E2EFDA]IRA,Joint[/td][td=bgcolor:#E2EFDA]0.5,0.8[/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]John[/td][td=bgcolor:#E2EFDA]Q[/td][td=bgcolor:#E2EFDA]Smith[/td][td=bgcolor:#E2EFDA]123 Main St,124 Main St[/td][td=bgcolor:#E2EFDA]Anytown[/td][td=bgcolor:#E2EFDA]ST[/td][td=bgcolor:#E2EFDA]1234[/td][td=bgcolor:#E2EFDA]555-666-7777[/td][td=bgcolor:#E2EFDA]555-888-9999[/td][td=bgcolor:#E2EFDA]someone@nowhere.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B1,B2[/td][td]B[/td][td]Another Note[/td][td]IRA,Trust[/td][td]0.45,0.88[/td][td]1,2[/td][td]A[/td][td]N [/td][td]Other[/td][td]555 B Street[/td][td]Sometown[/td][td]ST,st[/td][td]11223[/td][td]555-888-96969,555-888-9669[/td][td][/td][td]email@email.com[/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UID", type text}, {"HH", type text}, {"Notes", type text}, {"Account", type text}, {"Fee", type number}, {"IMA Number", Int64.Type}, {"First", type text}, {"Middle", type text}, {"Last", type text}, {"Address", type text}, {"City", type text}, {"State", type text}, {"Zip", Int64.Type}, {"HOME Phone", type text}, {"CELL Phone", type text}, {"E-Mail Address", type text}, {"Address2", type text}, {"City3", type text}, {"State4", type text}, {"Zip5", Int64.Type}, {"2nd First", type text}, {"2nd Middle", type text}, {"2nd Last", type text}, {"2nd Address", type any}, {"2nd City", type any}, {"2nd State", type any}, {"2nd Zip", type any}, {"2nd HOME Phone", type any}, {"2nd CELL Phone", type text}, {"2nd E-Mail Address", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"HH"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "UID", each Table.Column([Count],"UID")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"UID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Notes", each Table.Column([Count],"Notes")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Notes", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values1", "Account", each Table.Column([Count],"Account")),
    #"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"Account", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom3" = Table.AddColumn(#"Extracted Values2", "Fee", each Table.Column([Count],"Fee")),
    #"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Fee", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom4" = Table.AddColumn(#"Extracted Values3", "IMA Number", each List.Distinct(Table.Column([Count],"IMA Number"))),
    #"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"IMA Number", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom5" = Table.AddColumn(#"Extracted Values4", "First", each List.Distinct(Table.Column([Count],"First"))),
    #"Extracted Values5" = Table.TransformColumns(#"Added Custom5", {"First", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom6" = Table.AddColumn(#"Extracted Values5", "Middle", each List.Distinct(Table.Column([Count],"Middle"))),
    #"Extracted Values6" = Table.TransformColumns(#"Added Custom6", {"Middle", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom7" = Table.AddColumn(#"Extracted Values6", "Last", each List.Distinct(Table.Column([Count],"Last"))),
    #"Extracted Values7" = Table.TransformColumns(#"Added Custom7", {"Last", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom8" = Table.AddColumn(#"Extracted Values7", "Address", each List.Distinct(Table.Column([Count],"Address"))),
    #"Extracted Values8" = Table.TransformColumns(#"Added Custom8", {"Address", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom9" = Table.AddColumn(#"Extracted Values8", "City", each List.Distinct(Table.Column([Count],"City"))),
    #"Extracted Values9" = Table.TransformColumns(#"Added Custom9", {"City", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom10" = Table.AddColumn(#"Extracted Values9", "State", each List.Distinct(Table.Column([Count],"State"))),
    #"Extracted Values10" = Table.TransformColumns(#"Added Custom10", {"State", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom11" = Table.AddColumn(#"Extracted Values10", "Zip", each List.Distinct(Table.Column([Count],"Zip"))),
    #"Extracted Values11" = Table.TransformColumns(#"Added Custom11", {"Zip", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom12" = Table.AddColumn(#"Extracted Values11", "HOME Phone", each List.Distinct(Table.Column([Count],"HOME Phone"))),
    #"Extracted Values12" = Table.TransformColumns(#"Added Custom12", {"HOME Phone", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom13" = Table.AddColumn(#"Extracted Values12", "CELL Phone", each List.Distinct(Table.Column([Count],"CELL Phone"))),
    #"Extracted Values13" = Table.TransformColumns(#"Added Custom13", {"CELL Phone", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom14" = Table.AddColumn(#"Extracted Values13", "E-Mail Address", each List.Distinct(Table.Column([Count],"E-Mail Address"))),
    #"Extracted Values14" = Table.TransformColumns(#"Added Custom14", {"E-Mail Address", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Reordered Columns" = Table.ReorderColumns(#"Extracted Values14",{"UID", "HH", "Count", "Notes", "Account", "Fee", "IMA Number", "First", "Middle", "Last", "Address", "City", "State", "Zip", "HOME Phone", "CELL Phone", "E-Mail Address"})
in
    #"Reordered Columns"[/SIZE]

IMHO it does not make much sense to draw all the columns from a grouped table, but if you need it you can do it.

Is that what you want?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA code for inserting multiple rows, copying and transposing data
2
Replies
15
Views
2K
Peter_SSs
Peter_SSs
O
  • Question Question
Macro To Sort Data Required
2
Replies
13
Views
1K
sheetspread
S
P
  • Question Question
How to transpose data from column to rows multiple times - macro?
Replies
1
Views
4K
mhillmann
mhillmann
J
  • Question Question
Transpose large data set
Replies
1
Views
5K
Special-K99
S
Y
  • Question Question
Need Help with a Macro to Transpose Data PLEASE!!!
Replies
1
Views
336
circledchicken
C

Forum statistics

Threads
1,223,719
Messages
6,174,089
Members
452,542
Latest member
Bricklin

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