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:

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.
 
Sort by date Sort by votes
example:

Column1CustomCustom.1
A1AA1
AA2AAAA2
AAA1AAAAAA1
B2BB2
B2BBBB3,BB4
BB3CCCC1,CC2
BB4
AA2
CC1
CC2

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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
UIDHHNotesAccountFeeIMA NumberFirstMiddleLastAddressCityStateZipHOME PhoneCELL PhoneE-Mail AddressAddress2City3State4Zip52nd First2nd Middle2nd Last2nd Address2nd City2nd State2nd Zip2nd HOME Phone2nd CELL Phone2nd E-Mail Address
A1AThis is a noteIRA
0.5​
1​
JohnQSmith123 Main StAnytownST
1234​
555-666-7777555-888-9999someone@nowhere.com789 1st StreetAnother TownST
777888​
JaneTSmith555-888-9977jane@email.com
A2AJoint
0.8​
1​
JohnQSmith124 Main StAnytownST
1234​
555-666-7777555-888-9999someone@nowhere.com789 1st StreetAnother TownST
777888​
JaneTSmith555-888-9977jane@email.com
B1BAnother NoteIRA
0.45​
1​
AN Other555 B StreetSometownST
11223​
555-888-96969email@email.com
B2BTrust
0.88​
2​
AN Other555 B StreetSometownst
11223​
555-888-9669

partial result
UIDHHNotesAccountFeeIMA NumberFirstMiddleLastAddressCityStateZipHOME PhoneCELL PhoneE-Mail Address
A1,A2AThis is a noteIRA,Joint0.5,0.81JohnQSmith123 Main St,124 Main StAnytownST1234555-666-7777555-888-9999someone@nowhere.com
B1,B2BAnother NoteIRA,Trust0.45,0.881,2AN Other555 B StreetSometownST,st11223555-888-96969,555-888-9669email@email.com

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
Macro To Sort Data Required
2
Replies
13
Views
1K
sheetspread
S
P
  • Question
How to transpose data from column to rows multiple times - macro?
Replies
1
Views
3K
mhillmann
mhillmann
J
  • Question
Transpose large data set
Replies
1
Views
5K
Special-K99
S
Y
  • Question
Need Help with a Macro to Transpose Data PLEASE!!!
Replies
1
Views
284
circledchicken
C

Forum statistics

Threads
1,214,648
Messages
6,120,726
Members
448,987
Latest member
marion_davis

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