Transpose colums to a row based on a keyword / number

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:

HHName AccountFee
1JonesIRA0.5%
1JonesIndividual0.8%
2SmithJoint1.0%
2SmithIRA0.5%
2
SmithTrust0.9%

<tbody>
</tbody>




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:


HHNameAccount1Fee1Account2Fee2Account3Fee3
1JonesIRA0.5%Individual0.8%
2SmithJoint1.0%IRA0.5%Trust0.9%

<tbody>
</tbody>



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.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,849
Try PowerQuery (aka Get&Transform)

HHNameAccountFeeHHNameAccount.1Fee.1Account.2Fee.2Account.3Fee.3
1​
JonesIRA
0.50%​
1​
JonesIRA
0.50%​
Individual
0.80%​
1​
JonesIndividual
0.80%​
2​
SmithJoint
1.00%​
IRA
0.50%​
Trust
0.90%​
2​
SmithJoint
1.00%​
2​
SmithIRA
0.50%​
2​
SmithTrust
0.90%​
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]
 

corruptedlogic

New Member
Joined
Mar 8, 2018
Messages
12
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!
 

corruptedlogic

New Member
Joined
Mar 8, 2018
Messages
12
Strike that, i'm an idiot. My HH column was not configured correctly...this should work great. Thanks for the help.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,849
You are welcome
 

corruptedlogic

New Member
Joined
Mar 8, 2018
Messages
12
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?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,849
this is sorted by HH.
with your example change Jones to Zones then refresh result table (Ctrl+Alt+F5)
 

corruptedlogic

New Member
Joined
Mar 8, 2018
Messages
12
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:
HH
Name
Account
Fee
1
Jones
IRA
0.8
1
Zones
Joint
0.5

<tbody>
</tbody>

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,
 

corruptedlogic

New Member
Joined
Mar 8, 2018
Messages
12
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!
 

Forum statistics

Threads
1,082,551
Messages
5,366,276
Members
400,881
Latest member
DevelopedUnkown

Some videos you may like

This Week's Hot Topics

Top