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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
using PowerQuery (Get&Transform)

Customer ID Company Name Name Email
PUS0000001 Yoga Town Kim Jones sara@gmail.com
PUS0000001 Yoga Town Dan Porter dan@gmail.com
Customer IDCompany NameName.1Email.1Name.2Email.2
PUS0000001Yoga TownKim Jonessara@gmail.comDan Porterdan@gmail.com

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?

Customer ID Company Name Name EmailCustomer IDCompany NameName.1Name.2Name.3Name.4Name.5Email.1Email.2Email.3Email.4Email.5
PUS0000001 Yoga Town Kim Jones kim@yoga.comPUS0000001Yoga TownKim JonesDan PorterJohn Doekim@yoga.comdan@yoga.comjohn@yoga.com
PUS0000001 Yoga Town Dan Porterdan@yoga.comPUS0000002GovernmentLee OswaldJack Rubylee@government.govjack@government.gov
PUS0000001Yoga Town John Doejohn@yoga.comPUS0000003CandyEddie DuncanLarry RogersMichael McintoshJimmy Ray PayneJohn Portereddie@candy.infolarry@cany.infomichael@cany.infojimmi@cany.infojohn@cany.info
PUS0000002GovernmentLee Oswaldlee@government.gov
PUS0000002GovernmentJack Rubyjack@government.gov
PUS0000003CandyEddie Duncaneddie@candy.info
PUS0000003CandyLarry Rogerslarry@cany.info
PUS0000003CandyMichael Mcintoshmichael@cany.info
PUS0000003CandyJimmy Ray Paynejimmi@cany.info
PUS0000003CandyJohn Porterjohn@cany.info
 
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...

Company NameCustomer IDName.1Email.1Name.2Email.2Name.3Email.3Name.4Email.4Name.5Email.5
Yoga TownPUS0000001Kim Joneskim@yoga.comDan Porterdan@yoga.comJohn Doejohn@yoga.com
GovernmentPUS0000002Lee Oswaldlee@government.govJack Rubyjack@government.gov
CandyPUS0000003Eddie Duncaneddie@candy.infoLarry Rogerslarry@candy.infoMichael Mcintoshmichael@candy.infoJimmy Ray Paynejimmi@candy.infoJohn Porterjohn@candy.info

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
Translating a horizontal data set to a pivot table friendly vertical format
Replies
6
Views
883
TaxSloth
T
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
1K
Alex Blakenburg
A
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
377
AndyEd
A
R
  • Question
VLOOKUP across two sheets, first sheet has data vertically, second horizontally
Replies
9
Views
849
Fluff
Fluff
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A

Forum statistics

Threads
1,214,430
Messages
6,119,453
Members
448,898
Latest member
drewmorgan128

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