Possibly Simple - Pivoting Vertical to Horizontal

AnetShe

New Member
Joined
Apr 27, 2017
Messages
13
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!!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,812
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]
 

AnetShe

New Member
Joined
Apr 27, 2017
Messages
13
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?
 

AnetShe

New Member
Joined
Apr 27, 2017
Messages
13
*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.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,812

ADVERTISEMENT

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
 

AnetShe

New Member
Joined
Apr 27, 2017
Messages
13
Yes exactly! With unlimited (in my case 101) name & email columns at the right side.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,812

ADVERTISEMENT

if

Yes exactly! With unlimited (in my case 101) name & email columns at the right side.

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]
 

AnetShe

New Member
Joined
Apr 27, 2017
Messages
13
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.?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,812
I said: if "exactly"....

you didn't read post

for sort columns you need to wait... I've limited time
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,812
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,429
Members
409,876
Latest member
Akash Yadav
Top