Possibly Simple - Pivoting Vertical to Horizontal

A

AnetShe

New Member
Joined
Apr 27, 2017
Messages
20
I'm stuck on what I think is a simple issue and can’t seem to figure out what is needed – any help is appreciated!
I am looking to take a vertical list of data and pivot only two columns into stretching across the row, while keeping only one line for each unique Customer ID. My list has about 2000 rows of data with about 1000 unique IDs.

The original data looks like:

Customer ID | Company Name | Name | Email
PUS0000001 | Yoga Town | Kim Jones | sara@gmail.com
PUS0000001 | Yoga Town | Dan Porter| dan@gmail.com

I am looking to switch the format to:

Customer ID | Company Name | Name | Email | Name | EmailPUS0000001 | Yoga Town | Kim Jones | sara@gmail.com | Kim Jones | sara@gmail.com


Let me know if you have questions and THANK YOU!!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
using PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Customer ID [/td][td=bgcolor:#5B9BD5] Company Name [/td][td=bgcolor:#5B9BD5] Name [/td][td=bgcolor:#5B9BD5] Email[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PUS0000001 [/td][td=bgcolor:#DDEBF7] Yoga Town [/td][td=bgcolor:#DDEBF7] Kim Jones [/td][td=bgcolor:#DDEBF7] sara@gmail.com[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PUS0000001 [/td][td] Yoga Town [/td][td] Dan Porter[/td][td] dan@gmail.com[/td][td][/td][td][/td][/tr]

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

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Customer ID[/td][td=bgcolor:#70AD47]Company Name[/td][td=bgcolor:#70AD47]Name.1[/td][td=bgcolor:#70AD47]Email.1[/td][td=bgcolor:#70AD47]Name.2[/td][td=bgcolor:#70AD47]Email.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]PUS0000001[/td][td=bgcolor:#E2EFDA]Yoga Town[/td][td=bgcolor:#E2EFDA]Kim Jones[/td][td=bgcolor:#E2EFDA]sara@gmail.com[/td][td=bgcolor:#E2EFDA]Dan Porter[/td][td=bgcolor:#E2EFDA]dan@gmail.com[/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table22"]}[Content],
    Demote = Table.DemoteHeaders(Source),
    Trim = Table.TransformColumns(Demote,{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}, {"Column3", Text.Trim, type text}, {"Column4", Text.Trim, type text}}),
    Promote = Table.PromoteHeaders(Trim, [PromoteAllScalars=true]),
    Group = Table.Group(Promote, {"Customer ID", "Company Name"}, {{"Count", each _, type table}}),
    Name = Table.AddColumn(Group, "Name", each Table.Column([Count],"Name")),
    Email = Table.AddColumn(Name, "Email", each Table.Column([Count],"Email")),
    ExtractName = Table.TransformColumns(Email, {"Name", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    ExtractEmail = Table.TransformColumns(ExtractName, {"Email", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    SplitName = Table.SplitColumn(ExtractEmail, "Name", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Name.1", "Name.2"}),
    SplitEmail = Table.SplitColumn(SplitName, "Email", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Email.1", "Email.2"}),
    ROC = Table.SelectColumns(SplitEmail,{"Customer ID", "Company Name", "Name.1", "Email.1", "Name.2", "Email.2"})
in
    ROC[/SIZE]
 
Upvote 0
Thank you! This worked for pulling out two of the names & emails, but what I forgot to mention was that I have up 101 name & emails per Customer ID.
Is there a way to have these run along the row without having to add name & email 101 times in the code?
 
Upvote 0
*Adding an update - I forgot to mention my file has up to 101 name & emails per Customer ID, I have just shown two name & emails in my example.
 
Upvote 0
something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Customer ID [/td][td=bgcolor:#5B9BD5] Company Name [/td][td=bgcolor:#5B9BD5] Name [/td][td=bgcolor:#5B9BD5] Email[/td][td][/td][td=bgcolor:#70AD47]Customer ID[/td][td=bgcolor:#70AD47]Company Name[/td][td=bgcolor:#70AD47]Name.1[/td][td=bgcolor:#70AD47]Name.2[/td][td=bgcolor:#70AD47]Name.3[/td][td=bgcolor:#70AD47]Name.4[/td][td=bgcolor:#70AD47]Name.5[/td][td=bgcolor:#70AD47]Email.1[/td][td=bgcolor:#70AD47]Email.2[/td][td=bgcolor:#70AD47]Email.3[/td][td=bgcolor:#70AD47]Email.4[/td][td=bgcolor:#70AD47]Email.5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PUS0000001 [/td][td=bgcolor:#DDEBF7]Yoga Town [/td][td=bgcolor:#DDEBF7]Kim Jones [/td][td=bgcolor:#DDEBF7]kim@yoga.com[/td][td][/td][td=bgcolor:#E2EFDA]PUS0000001[/td][td=bgcolor:#E2EFDA]Yoga Town[/td][td=bgcolor:#E2EFDA]Kim Jones[/td][td=bgcolor:#E2EFDA]Dan Porter[/td][td=bgcolor:#E2EFDA]John Doe[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]kim@yoga.com[/td][td=bgcolor:#E2EFDA]dan@yoga.com[/td][td=bgcolor:#E2EFDA]john@yoga.com[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PUS0000001 [/td][td]Yoga Town [/td][td]Dan Porter[/td][td]dan@yoga.com[/td][td][/td][td]PUS0000002[/td][td]Government[/td][td]Lee Oswald[/td][td]Jack Ruby[/td][td][/td][td][/td][td][/td][td]lee@government.gov[/td][td]jack@government.gov[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PUS0000001[/td][td=bgcolor:#DDEBF7]Yoga Town [/td][td=bgcolor:#DDEBF7]John Doe[/td][td=bgcolor:#DDEBF7]john@yoga.com[/td][td][/td][td=bgcolor:#E2EFDA]PUS0000003[/td][td=bgcolor:#E2EFDA]Candy[/td][td=bgcolor:#E2EFDA]Eddie Duncan[/td][td=bgcolor:#E2EFDA]Larry Rogers[/td][td=bgcolor:#E2EFDA]Michael Mcintosh[/td][td=bgcolor:#E2EFDA]Jimmy Ray Payne[/td][td=bgcolor:#E2EFDA]John Porter[/td][td=bgcolor:#E2EFDA]eddie@candy.info[/td][td=bgcolor:#E2EFDA]larry@cany.info[/td][td=bgcolor:#E2EFDA]michael@cany.info[/td][td=bgcolor:#E2EFDA]jimmi@cany.info[/td][td=bgcolor:#E2EFDA]john@cany.info[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PUS0000002[/td][td]Government[/td][td]Lee Oswald[/td][td]lee@government.gov[/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]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PUS0000002[/td][td=bgcolor:#DDEBF7]Government[/td][td=bgcolor:#DDEBF7]Jack Ruby[/td][td=bgcolor:#DDEBF7]jack@government.gov[/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]

[tr=bgcolor:#FFFFFF][td]PUS0000003[/td][td]Candy[/td][td]Eddie Duncan[/td][td]eddie@candy.info[/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]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PUS0000003[/td][td=bgcolor:#DDEBF7]Candy[/td][td=bgcolor:#DDEBF7]Larry Rogers[/td][td=bgcolor:#DDEBF7]larry@cany.info[/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]

[tr=bgcolor:#FFFFFF][td]PUS0000003[/td][td]Candy[/td][td]Michael Mcintosh[/td][td]michael@cany.info[/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]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PUS0000003[/td][td=bgcolor:#DDEBF7]Candy[/td][td=bgcolor:#DDEBF7]Jimmy Ray Payne[/td][td=bgcolor:#DDEBF7]jimmi@cany.info[/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]

[tr=bgcolor:#FFFFFF][td]PUS0000003[/td][td]Candy[/td][td]John Porter[/td][td]john@cany.info[/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]
 
Upvote 0
if

AnetShe said:
Yes exactly! With unlimited (in my case 101) name & email columns at the right side.
Click to expand...

here is:

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table22"]}[Content],
    Demote = Table.DemoteHeaders(Source),
    Trim = Table.TransformColumns(Demote,{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}, {"Column3", Text.Trim, type text}, {"Column4", Text.Trim, type text}}),
    Promote = Table.PromoteHeaders(Trim, [PromoteAllScalars=true]),
    Group = Table.Group(Promote, {"Customer ID", "Company Name"}, {{"Count", each _, type table}}),
    Name = Table.AddColumn(Group, "Name", each Table.Column([Count],"Name")),
    Email = Table.AddColumn(Name, "Email", each Table.Column([Count],"Email")),

    ExtractEmail = Table.TransformColumns(Email, {"Email", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    DelimiterCountEmail = Table.AddColumn(ExtractEmail, "CountDelimitersEmail", each List.Count(Text.Split([Email],"="))-1),
    MaxCountEmail = List.Max(DelimiterCountEmail[CountDelimitersEmail])+1,
SplitEmail = Table.SplitColumn(ExtractEmail,"Email", Splitter.SplitTextByDelimiter("="), MaxCountEmail),

    ExtractName = Table.TransformColumns(SplitEmail, {"Name", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    DelimiterCountName = Table.AddColumn(ExtractName, "CountDelimitersName", each List.Count(Text.Split([Name],"="))-1),
    MaxCountName = List.Max(DelimiterCountName[CountDelimitersName])+1,
SplitName = Table.SplitColumn(ExtractName,"Name", Splitter.SplitTextByDelimiter("="), MaxCountName)
in
    SplitName[/SIZE]
 
Upvote 0
sandy666 - this code worked but puts all the names first then the emails at end - is there a way to do name, email, name, email, name, email etc.?
 
Upvote 0
I said: if "exactly"....

you didn't read post

for sort columns you need to wait... I've limited time
 
Upvote 0
hope this is what you want...

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Company Name[/td][td=bgcolor:#70AD47]Customer ID[/td][td=bgcolor:#70AD47]Name.1[/td][td=bgcolor:#70AD47]Email.1[/td][td=bgcolor:#70AD47]Name.2[/td][td=bgcolor:#70AD47]Email.2[/td][td=bgcolor:#70AD47]Name.3[/td][td=bgcolor:#70AD47]Email.3[/td][td=bgcolor:#70AD47]Name.4[/td][td=bgcolor:#70AD47]Email.4[/td][td=bgcolor:#70AD47]Name.5[/td][td=bgcolor:#70AD47]Email.5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Yoga Town[/td][td=bgcolor:#E2EFDA]PUS0000001[/td][td=bgcolor:#E2EFDA]Kim Jones[/td][td=bgcolor:#E2EFDA]kim@yoga.com[/td][td=bgcolor:#E2EFDA]Dan Porter[/td][td=bgcolor:#E2EFDA]dan@yoga.com[/td][td=bgcolor:#E2EFDA]John Doe[/td][td=bgcolor:#E2EFDA]john@yoga.com[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Government[/td][td]PUS0000002[/td][td]Lee Oswald[/td][td]lee@government.gov[/td][td]Jack Ruby[/td][td]jack@government.gov[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Candy[/td][td=bgcolor:#E2EFDA]PUS0000003[/td][td=bgcolor:#E2EFDA]Eddie Duncan[/td][td=bgcolor:#E2EFDA]eddie@candy.info[/td][td=bgcolor:#E2EFDA]Larry Rogers[/td][td=bgcolor:#E2EFDA]larry@candy.info[/td][td=bgcolor:#E2EFDA]Michael Mcintosh[/td][td=bgcolor:#E2EFDA]michael@candy.info[/td][td=bgcolor:#E2EFDA]Jimmy Ray Payne[/td][td=bgcolor:#E2EFDA]jimmi@candy.info[/td][td=bgcolor:#E2EFDA]John Porter[/td][td=bgcolor:#E2EFDA]john@candy.info[/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table22"]}[Content],
    Demote = Table.DemoteHeaders(Source),
    Trim = Table.TransformColumns(Demote,{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}, {"Column3", Text.Trim, type text}, {"Column4", Text.Trim, type text}}),
    Promote = Table.PromoteHeaders(Trim, [PromoteAllScalars=true]),
    Group = Table.Group(Promote, {"Customer ID", "Company Name"}, {{"Count", each _, type table}}),
    Name = Table.AddColumn(Group, "Name", each Table.Column([Count],"Name")),
    Email = Table.AddColumn(Name, "Email", each Table.Column([Count],"Email")),

    ExtractEmail = Table.TransformColumns(Email, {"Email", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    DelimiterCountEmail = Table.AddColumn(ExtractEmail, "CountDelimitersEmail", each List.Count(Text.Split([Email],"="))-1),
    MaxCountEmail = List.Max(DelimiterCountEmail[CountDelimitersEmail])+1,
SplitEmail = Table.SplitColumn(ExtractEmail,"Email", Splitter.SplitTextByDelimiter("="), MaxCountEmail),

    ExtractName = Table.TransformColumns(SplitEmail, {"Name", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    DelimiterCountName = Table.AddColumn(ExtractName, "CountDelimitersName", each List.Count(Text.Split([Name],"="))-1),
    MaxCountName = List.Max(DelimiterCountName[CountDelimitersName])+1,
SplitName = Table.SplitColumn(ExtractName,"Name", Splitter.SplitTextByDelimiter("="), MaxCountName),

    RenCol00 = Table.RenameColumns(SplitName,{{"Customer ID", "00Customer ID"}, {"Company Name", "00Company Name"}}),
    RC = Table.RemoveColumns(RenCol00,{"Count"}),
    Demote2 = Table.DemoteHeaders(RC),
    Transpose = Table.Transpose(Demote2),
    IF1 = Table.AddColumn(Transpose, "Custom", each if Text.Contains([Column1], ".") then Text.AfterDelimiter([Column1], ".") else null),
    IF2 = Table.AddColumn(IF1, "Custom.1", each if Text.Contains([Column1], "Name.") then "A" else if Text.Contains([Column1], "Email.") then "B" else null),
    Merge = Table.CombineColumns(IF2,{"Custom", "Custom.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    Sort = Table.Sort(Merge,{{"Merged", Order.Ascending}}),
    RC2 = Table.RemoveColumns(Sort,{"Merged"}),
    Transpose2 = Table.Transpose(RC2),
    Promote2 = Table.PromoteHeaders(Transpose2, [PromoteAllScalars=true]),
    RenCols = Table.RenameColumns(Promote2,{{"00Company Name", "Company Name"}, {"00Customer ID", "Customer ID"}})
in
    RenCols[/SIZE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Translating a horizontal data set to a pivot table friendly vertical format
Replies
6
Views
2K
TaxSloth
T
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
127
Fluff
Fluff
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
R
  • Question Question
VLOOKUP across two sheets, first sheet has data vertically, second horizontally
Replies
9
Views
964
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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