UN-pivoting a pivoted range

capson

Board Regular
Joined
Jul 9, 2010
Messages
107
Hello, I have data in the shape

Code:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]Name From[/TD]
[TD]Name To[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]Alisa Morales[/TD]
[TD]Elwood Iglesias[/TD]
[TD]value two[/TD]
[/TR]
[TR]
[TD]Elwood Iglesias[/TD]
[TD]Alisa Morales[/TD]
[TD]value one[/TD]
[/TR]
[TR]
[TD]Elwood Iglesias[/TD]
[TD]Georgette Harrison[/TD]
[TD]value three[/TD]
[/TR]
[TR]
[TD]Elwood Iglesias[/TD]
[TD]Fannie Fuentes[/TD]
[TD]value four[/TD]
[/TR]
[TR]
[TD]Fannie Fuentes[/TD]
[TD]Alisa Morales[/TD]
[TD]value three[/TD]
[/TR]
[TR]
[TD]Fannie Fuentes[/TD]
[TD]Georgette Harrison[/TD]
[TD]Value five[/TD]
[/TR]
[TR]
[TD]Georgette Harrison[/TD]
[TD]Alisa Morales[/TD]
[TD]value four[/TD]
[/TR]
[TR]
[TD]Georgette Harrison[/TD]
[TD]Elwood Iglesias[/TD]
[TD]value two[/TD]
[/TR]
[TR]
[TD]Georgette Harrison[/TD]
[TD]Fannie Fuentes[/TD]
[TD]Value five[/TD]
[/TR]
[TR]
[TD]Sam Spade[/TD]
[TD]Fannie Fuentes[/TD]
[TD]value one[/TD]
[/TR]
[TR]
[TD]Sam Spade[/TD]
[TD]Elwood Iglesias[/TD]
[TD]value two[/TD]
[/TR]
[TR]
[TD]Sam Spade[/TD]
[TD]Alisa Morales[/TD]
[TD]value two[/TD]
[/TR]
[TR]
[TD]Sam Spade[/TD]
[TD]Georgette Harrison[/TD]
[TD]value four[/TD]
[/TR]
[TR]
[TD]Sam Spade[/TD]
[TD]Maxy Max[/TD]
[TD]value five[/TD]
[/TR]
[TR]
[TD]Maxy Max[/TD]
[TD]Sam Spade[/TD]
[TD]value five[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

I need to UN-pivot it so it becomes

Code:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]Participant Name[/TD]
[TD]Participant relationship #1[/TD]
[TD]Type #1[/TD]
[TD]Participant relationship #2[/TD]
[TD]Type #2[/TD]
[TD]Participant relationship #3[/TD]
[TD]Type #3[/TD]
[TD]Participant relationship #4[/TD]
[TD]Type #4[/TD]
[TD]Participant relationship #5[/TD]
[TD]Type #5[/TD]
[/TR]
[TR]
[TD]Alisa Morales[/TD]
[TD]Elwood Iglesias[/TD]
[TD]Value Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elwood Iglesias[/TD]
[TD]Alisa Morales[/TD]
[TD]Value one[/TD]
[TD]Georgette Harrison[/TD]
[TD]value three[/TD]
[TD]Fannie Fuentes[/TD]
[TD]value four[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fannie Fuentes[/TD]
[TD]Alisa Morales[/TD]
[TD]Value three[/TD]
[TD]Georgette Harrison[/TD]
[TD]value five[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Georgette Harrison[/TD]
[TD]alisa Morales[/TD]
[TD]value four[/TD]
[TD]Elwood Iglesias[/TD]
[TD]Value two[/TD]
[TD]Fannie Fuentes[/TD]
[TD]value five[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam Spade[/TD]
[TD]Fanny Fuentes[/TD]
[TD]value one[/TD]
[TD]Elwood Iglesias[/TD]
[TD]value two[/TD]
[TD]Alisa Morales[/TD]
[TD]value one[/TD]
[TD]Georgette Harriason[/TD]
[TD]Value four[/TD]
[TD]Maxy Max[/TD]
[TD]Value five[/TD]
[/TR]
[TR]
[TD]Maxy Max[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sam Spade[/TD]
[TD]Value Five[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the the real data the number of times a name can repeat itself is between 100 and 200 (so Sam Spade might have up to 160 connections) and there can be up to 50,000 rows of data in total

I have been trying be can't get anywhere with this:oops:

Thank you

Here is a Google sheet with this data
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
maybe

Name FromName ToTypeName FromName To.1Type.1Name To.2Type.2Name To.3Type.3Name To.4Type.4Name To.5Type.5
Alisa MoralesElwood Iglesiasvalue twoAlisa MoralesElwood Iglesiasvalue two
Elwood IglesiasAlisa Moralesvalue oneElwood IglesiasAlisa Moralesvalue oneGeorgette Harrisonvalue threeFannie Fuentesvalue four
Elwood IglesiasGeorgette Harrisonvalue threeFannie FuentesAlisa Moralesvalue threeGeorgette HarrisonValue five
Elwood IglesiasFannie Fuentesvalue fourGeorgette HarrisonAlisa Moralesvalue fourElwood Iglesiasvalue twoFannie FuentesValue five
Fannie FuentesAlisa Moralesvalue threeSam SpadeFannie Fuentesvalue oneElwood Iglesiasvalue twoAlisa Moralesvalue twoGeorgette Harrisonvalue fourMaxy Maxvalue five
Fannie FuentesGeorgette HarrisonValue fiveMaxy MaxSam Spadevalue five
Georgette HarrisonAlisa Moralesvalue four
Georgette HarrisonElwood Iglesiasvalue two
Georgette HarrisonFannie FuentesValue five
Sam SpadeFannie Fuentesvalue one
Sam SpadeElwood Iglesiasvalue two
Sam SpadeAlisa Moralesvalue two
Sam SpadeGeorgette Harrisonvalue four
Sam SpadeMaxy Maxvalue five
Maxy MaxSam Spadevalue five

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name From"}, {{"Count", each _, type table}}),
    List1 = Table.AddColumn(Group, "Name To", each Table.Column([Count],"Name To")),
    List2 = Table.AddColumn(List1, "Type", each Table.Column([Count],"Type")),
    Extract1 = Table.TransformColumns(List2, {"Name To", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Extract2 = Table.TransformColumns(Extract1, {"Type", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split1 = Table.SplitColumn(Extract2, "Name To", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Name To.1", "Name To.2", "Name To.3", "Name To.4", "Name To.5"}),
    Split2 = Table.SplitColumn(Split1, "Type", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Type.1", "Type.2", "Type.3", "Type.4", "Type.5"}),
    ROC = Table.SelectColumns(Split2,{"Name From", "Name To.1", "Type.1", "Name To.2", "Type.2", "Name To.3", "Type.3", "Name To.4", "Type.4", "Name To.5", "Type.5"})
in
    ROC[/SIZE]
 
Upvote 0
Sandy666, thank you for the reply! but I do not understand it

I am not sure how to run it even as it does not appear to be VBA

If you could instruct me on how to run it that would be great!
 
Upvote 0
sandy666 is using PowerQuery (Get and Transform in later versions), what version of Excel are you using?
 
Upvote 0
Just to point out only the Professional Plus version of Excel 2010 in Excel 2010 and higher.
 
Upvote 0
Hello, I did figure out how to run it, thanks everyone

I am using Excel 360

It is working-kind of, in that, in my example data any name has at most 5 connections and that would be for Sam Spade

In my real data there are name that have up to 170 connections

Is there a way to make the code work on this amount of connection i.e. Sam Spade instead of 5 connection has 170 connections

Thanks
 
Last edited:
Upvote 0
Sandy,

I can't share the actual data with you because it's private & my job to keep it that way. However, the structure is exactly the same as in my example above.

The difference is that my dataset has ~365 unique 'from' names and each 'from' name has anywhere from 1-170 'to' names (each 'from-to' pair is 'a connection') - for a total of ~8,500 rows or ~8,500 'connections'.

I need to repeat the 'name.#' & 'type.#' as many times as the most-connected person has connections - so ~!70 times.

I am not sure how to post a more real data set then I have

Thank again
 
Last edited:
Upvote 0
ok, try this:

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name From"}, {{"Count", each _, type table}}),
    List1 = Table.AddColumn(Group, "Name To", each Table.Column([Count],"Name To")),
    List2 = Table.AddColumn(List1, "Type", each Table.Column([Count],"Type")),
    Extract1 = Table.TransformColumns(List2, {"Name To", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Extract2 = Table.TransformColumns(Extract1, {"Type", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SplitCount1 = Table.AddColumn(Extract1, "Split Count1", each List.Count(Text.Split([Name To],","))),
    MaxCount1 = List.Max(SplitCount1[Split Count1]),
    SplitCount2 = Table.AddColumn(Extract2, "Split Count2", each List.Count(Text.Split([Type],","))),
    MaxCount2 = List.Max(SplitCount2[Split Count2]),
    Split1 = Table.SplitColumn(Extract2, "Name To", Splitter.SplitTextByDelimiter(","), MaxCount1),
    Split2 = Table.SplitColumn(Split1, "Type", Splitter.SplitTextByDelimiter(","), MaxCount2),
    RC1 = Table.RemoveColumns(Split2,{"Count"}),
    Demote1 = Table.DemoteHeaders(RC1),
    Transpose1 = Table.Transpose(Demote1),
    Promote1 = Table.PromoteHeaders(Transpose1, [PromoteAllScalars=true]),
    Last = Table.AddColumn(Promote1, "Last Characters", each Text.End([Name From], 1), type text),
    First = Table.AddColumn(Last, "First Characters", each Text.Start([Name From], 1), type text),
    Merge = Table.CombineColumns(First,{"Last Characters", "First Characters"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    Sort = Table.Sort(Merge,{{"Merged", Order.Ascending}}),
    RC2 = Table.RemoveColumns(Sort,{"Merged"}),
    Demote2 = Table.DemoteHeaders(RC2),
    Transpose2 = Table.Transpose(Demote2),
    Promote2 = Table.PromoteHeaders(Transpose2, [PromoteAllScalars=true])
in
    Promote2[/SIZE]

doesn't matter how many connections you have and it should be sorted correctly also
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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