Transpose colums to a row based on a keyword / number

[corruptedlogic]

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:

HH	Name	Account	Fee
1	Jones	IRA	0.5%
1	Jones	Individual	0.8%
2	Smith	Joint	1.0%
2	Smith	IRA	0.5%
2	Smith	Trust	0.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:

HH	Name	Account1	Fee1	Account2	Fee2	Account3	Fee3
1	Jones	IRA	0.5%	Individual	0.8%
2	Smith	Joint	1.0%	IRA	0.5%	Trust	0.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]

Try PowerQuery (aka Get&Transform)

HH

Name

Account

Fee

HH

Name

Account.1

Fee.1

Account.2

Fee.2

Account.3

Fee.3

1​

Jones

IRA

0.50%​

1​

Jones

IRA

0.50%​

Individual

0.80%​

1​

Jones

Individual

0.80%​

2​

Smith

Joint

1.00%​

IRA

0.50%​

Trust

0.90%​

2​

Smith

Joint

1.00%​

2​

Smith

IRA

0.50%​

2​

Smith

Trust

0.90%​

[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]

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:

[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]

Strike that, i'm an idiot. My HH column was not configured correctly...this should work great. Thanks for the help.

 

[sandy666]

You are welcome

 

[corruptedlogic]

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]

this is sorted by HH.
with your example change Jones to Zones then refresh result table (Ctrl+Alt+F5)

 

[corruptedlogic]

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]

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!

 

[sandy666]

theory
duplicate HH
remove numbers from dup HH
group by letters
...