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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Try PowerQuery (aka Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]HH[/td][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Account[/td][td=bgcolor:#5B9BD5]Fee[/td][td][/td][td=bgcolor:#70AD47]HH[/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Account.1[/td][td=bgcolor:#70AD47]Fee.1[/td][td=bgcolor:#70AD47]Account.2[/td][td=bgcolor:#70AD47]Fee.2[/td][td=bgcolor:#70AD47]Account.3[/td][td=bgcolor:#70AD47]Fee.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]Jones[/td][td=bgcolor:#DDEBF7]IRA[/td][td=bgcolor:#DDEBF7]
0.50%​
[/td][td][/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]Jones[/td][td=bgcolor:#E2EFDA]IRA[/td][td=bgcolor:#E2EFDA]
0.50%​
[/td][td=bgcolor:#E2EFDA]Individual[/td][td=bgcolor:#E2EFDA]
0.80%​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]Jones[/td][td]Individual[/td][td]
0.80%​
[/td][td][/td][td]
2​
[/td][td]Smith[/td][td]Joint[/td][td]
1.00%​
[/td][td]IRA[/td][td]
0.50%​
[/td][td]Trust[/td][td]
0.90%​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7]Joint[/td][td=bgcolor:#DDEBF7]
1.00%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]Smith[/td][td]IRA[/td][td]
0.50%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7]Trust[/td][td=bgcolor:#DDEBF7]
0.90%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
Code: 
[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"HH", "Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Account", each Table.Column([Count],"Account")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Account", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Fee", each Table.Column([Count],"Fee")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Fee", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values1", "Account", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Account.1", "Account.2", "Account.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Account.1", type text}, {"Account.2", type text}, {"Account.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Fee", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Fee.1", "Fee.2", "Fee.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Fee.1", type number}, {"Fee.2", type number}, {"Fee.3", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"HH", "Name", "Count", "Account.1", "Fee.1", "Account.2", "Fee.2", "Account.3", "Fee.3"})
in
    #"Reordered Columns"[/SIZE]
 
Upvote 0
This would be perfect but for some reason it does not function (Power Queries are brand new to me so there is a good chance i am not doing something right here).

Here's my process:
Open the source excel sheet & define the dataset as a table called Table9 (to match the PQ).
Navigate to Data > New Query > From Other Source > Blank Query > Advanced Editor
Paste the above code.
That yields this:
Code: 
[TABLE]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD]HH[/TD]
[TD]Name[/TD]
[TD]Account.1[/TD]
[TD]Fee.1[/TD]
[TD]Account.2[/TD]
[TD]Fee.2[/TD]
[TD]Account.3[/TD]
[TD]Fee.3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Smith[/TD]
[TD]IRA[/TD]
[TD="align: right"]0.008[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD]Jones[/TD]
[TD="align: right"]529[/TD]
[TD="align: right"]0.005[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD]Martin[/TD]
[TD]Individual[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD]Stuvland[/TD]
[TD]IRA[/TD]
[TD="align: right"]0.0125[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD]Smith[/TD]
[TD]Individual[/TD]
[TD="align: right"]0.0075[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD]Suvland[/TD]
[TD]Joint[/TD]
[TD="align: right"]0.008[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD]Martin[/TD]
[TD]Trust[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD]Jones[/TD]
[TD]IRA[/TD]
[TD="align: right"]0.005[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD]Smith[/TD]
[TD]Trust[/TD]
[TD="align: right"]0.0055[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD]Stuvland[/TD]
[TD]Joint[/TD]
[TD="align: right"]0.006[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD]Jones[/TD]
[TD="align: right"]529[/TD]
[TD="align: right"]0.008[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD]Jones[/TD]
[TD]IRA[/TD]
[TD="align: right"]0.0047[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD]Martin[/TD]
[TD]Trust[/TD]
[TD="align: right"]0.003[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD]Martin[/TD]
[TD]Joint[/TD]
[TD="align: right"]0.0033[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD]Stuvland[/TD]
[TD]Individual[/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD]Smith[/TD]
[TD]Other[/TD]
[TD="align: right"]0.013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Sheet2[/B][/COLOR][/CENTER]

My guess is i am missing something. Thanks in advance!
 
Upvote 0
If i were to tweak this a little and want to iterate just by the HH column (not sort by name, just hh) but include the name column in the data...how might i go about that?
 
Upvote 0
this is sorted by HH.
with your example change Jones to Zones then refresh result table (Ctrl+Alt+F5)
 
Upvote 0
If i do that, i get a new row with 1 in HH and Zones in name, the goal is to keep all of the HH numbers on 1 row vs a new row if Name changes. for example:[TABLE="width: 500"]
<tbody>[TR]
[TD]HH
[/TD]
[TD]Name
[/TD]
[TD]Account
[/TD]
[TD]Fee
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Jones
[/TD]
[TD]IRA
[/TD]
[TD]0.8
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Zones
[/TD]
[TD]Joint
[/TD]
[TD]0.5
[/TD]
[/TR]
</tbody>[/TABLE]

Should both appear on the same row. We can drop the name (which actually makes more sense now i'm working through it in my mind, the account & fee are important columns)
So i guess what i'm really asking is, how do we exclude ignore the Name column?

thanks,
 
Upvote 0
Ok, i figured that piece out, i was able to change the PQ to get where i need to be. One more tweak, if the HH column were to contain more than numbers (ex: A1, A2, A3 all the way down to AAA1 etc), how would i tell it to group by the letters and ignore the numbers (ex: all A<whatever number> gets grouped, all B<whatever number> gets grouped down to AAA<whatever number> gets grouped?

Thank you!
 
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,715
Messages
6,174,064
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