UN-pivoting a pivoted range

C

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
 
Sort by date Sort by votes
Sandy, I tried the new query but did not give the desired output, but I was able to get your original post to work

I made a concatenation macro and this worked great

Code: 
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","Name To.6","Name To.7","Name To.8","Name To.9","Name To.10","Name To.11","Name To.12","Name To.13","Name To.14","Name To.15","Name To.16","Name To.17","Name To.18","Name To.19","Name To.20","Name To.21","Name To.22","Name To.23","Name To.24","Name To.25","Name To.26","Name To.27","Name To.28","Name To.29","Name To.30","Name To.31","Name To.32","Name To.33","Name To.34","Name To.35","Name To.36","Name To.37","Name To.38","Name To.39","Name To.40","Name To.41","Name To.42","Name To.43","Name To.44","Name To.45","Name To.46","Name To.47","Name To.48","Name To.49","Name To.50","Name To.51","Name To.52","Name To.53","Name To.54","Name To.55","Name To.56","Name To.57","Name To.58","Name To.59","Name To.60","Name To.61","Name To.62","Name To.63","Name To.64","Name To.65","Name To.66","Name To.67","Name To.68","Name To.69","Name To.70","Name To.71","Name To.72","Name To.73","Name To.74","Name To.75","Name To.76","Name To.77","Name To.78","Name To.79","Name To.80","Name To.81","Name To.82","Name To.83","Name To.84","Name To.85","Name To.86","Name To.87","Name To.88","Name To.89","Name To.90","Name To.91","Name To.92","Name To.93","Name To.94","Name To.95","Name To.96","Name To.97","Name To.98","Name To.99","Name To.100","Name To.101","Name To.102","Name To.103","Name To.104","Name To.105","Name To.106","Name To.107","Name To.108","Name To.109","Name To.110","Name To.111","Name To.112","Name To.113","Name To.114","Name To.115","Name To.116","Name To.117","Name To.118","Name To.119","Name To.120","Name To.121","Name To.122","Name To.123","Name To.124","Name To.125","Name To.126","Name To.127","Name To.128","Name To.129","Name To.130","Name To.131","Name To.132","Name To.133","Name To.134","Name To.135","Name To.136","Name To.137","Name To.138","Name To.139","Name To.140","Name To.141","Name To.142","Name To.143","Name To.144","Name To.145","Name To.146","Name To.147","Name To.148","Name To.149","Name To.150","Name To.151","Name To.152","Name To.153","Name To.154","Name To.155","Name To.156","Name To.157","Name To.158","Name To.159","Name To.160","Name To.161","Name To.162","Name To.163","Name To.164","Name To.165"}),
    Split2 = Table.SplitColumn(Split1, "Type", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Type.1","Type.2","Type.3","Type.4","Type.5","Type.6","Type.7","Type.8","Type.9","Type.10","Type.11","Type.12","Type.13","Type.14","Type.15","Type.16","Type.17","Type.18","Type.19","Type.20","Type.21","Type.22","Type.23","Type.24","Type.25","Type.26","Type.27","Type.28","Type.29","Type.30","Type.31","Type.32","Type.33","Type.34","Type.35","Type.36","Type.37","Type.38","Type.39","Type.40","Type.41","Type.42","Type.43","Type.44","Type.45","Type.46","Type.47","Type.48","Type.49","Type.50","Type.51","Type.52","Type.53","Type.54","Type.55","Type.56","Type.57","Type.58","Type.59","Type.60","Type.61","Type.62","Type.63","Type.64","Type.65","Type.66","Type.67","Type.68","Type.69","Type.70","Type.71","Type.72","Type.73","Type.74","Type.75","Type.76","Type.77","Type.78","Type.79","Type.80","Type.81","Type.82","Type.83","Type.84","Type.85","Type.86","Type.87","Type.88","Type.89","Type.90","Type.91","Type.92","Type.93","Type.94","Type.95","Type.96","Type.97","Type.98","Type.99","Type.100","Type.101","Type.102","Type.103","Type.104","Type.105","Type.106","Type.107","Type.108","Type.109","Type.110","Type.111","Type.112","Type.113","Type.114","Type.115","Type.116","Type.117","Type.118","Type.119","Type.120","Type.121","Type.122","Type.123","Type.124","Type.125","Type.126","Type.127","Type.128","Type.129","Type.130","Type.131","Type.132","Type.133","Type.134","Type.135","Type.136","Type.137","Type.138","Type.139","Type.140","Type.141","Type.142","Type.143","Type.144","Type.145","Type.146","Type.147","Type.148","Type.149","Type.150","Type.151","Type.152","Type.153","Type.154","Type.155","Type.156","Type.157","Type.158","Type.159","Type.160","Type.161","Type.162","Type.163","Type.164","Type.165"}),
    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","Name To.6","Type.6","Name To.7","Type.7","Name To.8","Type.8","Name To.9","Type.9","Name To.10","Type.10","Name To.11","Type.11","Name To.12","Type.12","Name To.13","Type.13","Name To.14","Type.14","Name To.15","Type.15","Name To.16","Type.16","Name To.17","Type.17","Name To.18","Type.18","Name To.19","Type.19","Name To.20","Type.20","Name To.21","Type.21","Name To.22","Type.22","Name To.23","Type.23","Name To.24","Type.24","Name To.25","Type.25","Name To.26","Type.26","Name To.27","Type.27","Name To.28","Type.28","Name To.29","Type.29","Name To.30","Type.30","Name To.31","Type.31","Name To.32","Type.32","Name To.33","Type.33","Name To.34","Type.34","Name To.35","Type.35","Name To.36","Type.36","Name To.37","Type.37","Name To.38","Type.38","Name To.39","Type.39","Name To.40","Type.40","Name To.41","Type.41","Name To.42","Type.42","Name To.43","Type.43","Name To.44","Type.44","Name To.45","Type.45","Name To.46","Type.46","Name To.47","Type.47","Name To.48","Type.48","Name To.49","Type.49","Name To.50","Type.50","Name To.51","Type.51","Name To.52","Type.52","Name To.53","Type.53","Name To.54","Type.54","Name To.55","Type.55","Name To.56","Type.56","Name To.57","Type.57","Name To.58","Type.58","Name To.59","Type.59","Name To.60","Type.60","Name To.61","Type.61","Name To.62","Type.62","Name To.63","Type.63","Name To.64","Type.64","Name To.65","Type.65","Name To.66","Type.66","Name To.67","Type.67","Name To.68","Type.68","Name To.69","Type.69","Name To.70","Type.70","Name To.71","Type.71","Name To.72","Type.72","Name To.73","Type.73","Name To.74","Type.74","Name To.75","Type.75","Name To.76","Type.76","Name To.77","Type.77","Name To.78","Type.78","Name To.79","Type.79","Name To.80","Type.80","Name To.81","Type.81","Name To.82","Type.82","Name To.83","Type.83","Name To.84","Type.84","Name To.85","Type.85","Name To.86","Type.86","Name To.87","Type.87","Name To.88","Type.88","Name To.89","Type.89","Name To.90","Type.90","Name To.91","Type.91","Name To.92","Type.92","Name To.93","Type.93","Name To.94","Type.94","Name To.95","Type.95","Name To.96","Type.96","Name To.97","Type.97","Name To.98","Type.98","Name To.99","Type.99","Name To.100","Type.100","Name To.101","Type.101","Name To.102","Type.102","Name To.103","Type.103","Name To.104","Type.104","Name To.105","Type.105","Name To.106","Type.106","Name To.107","Type.107","Name To.108","Type.108","Name To.109","Type.109","Name To.110","Type.110","Name To.111","Type.111","Name To.112","Type.112","Name To.113","Type.113","Name To.114","Type.114","Name To.115","Type.115","Name To.116","Type.116","Name To.117","Type.117","Name To.118","Type.118","Name To.119","Type.119","Name To.120","Type.120","Name To.121","Type.121","Name To.122","Type.122","Name To.123","Type.123","Name To.124","Type.124","Name To.125","Type.125","Name To.126","Type.126","Name To.127","Type.127","Name To.128","Type.128","Name To.129","Type.129","Name To.130","Type.130","Name To.131","Type.131","Name To.132","Type.132","Name To.133","Type.133","Name To.134","Type.134","Name To.135","Type.135","Name To.136","Type.136","Name To.137","Type.137","Name To.138","Type.138","Name To.139","Type.139","Name To.140","Type.140","Name To.141","Type.141","Name To.142","Type.142","Name To.143","Type.143","Name To.144","Type.144","Name To.145","Type.145","Name To.146","Type.146","Name To.147","Type.147","Name To.148","Type.148","Name To.149","Type.149","Name To.150","Type.150","Name To.151","Type.151","Name To.152","Type.152","Name To.153","Type.153","Name To.154","Type.154","Name To.155","Type.155","Name To.156","Type.156","Name To.157","Type.157","Name To.158","Type.158","Name To.159","Type.159","Name To.160","Type.160","Name To.161","Type.161","Name To.162","Type.162","Name To.163","Type.163","Name To.164","Type.164","Name To.165","Type.165"})
in
    ROC


thank you for all the work and effort you made, this saved-the-day-for-me

Again, Thanks
 
Last edited:
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
@capson
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
You must log in or register to reply here.

Similar threads

userxyz777
  • Question
Reverse pivot a pivoted range (Column to Matrix) using VBA
Replies
1
Views
477
mrshl9898
M
S
  • Question
Need help with a chart and formulas for making sentences based on similarity or differences, proximity and date of sales.
Replies
3
Views
681
Stumpped
S
L
  • Question
Dissecting a number string to correct format with IF AND IFS MID and getting rid of some but not all zeros
2
Replies
14
Views
2K
Lou Pointspreads
L
L
  • Question
How to translate an Excel text string if formula to a calculated field in access to match another tables format and be able to relate the tables
Replies
3
Views
1K
JonXL
JonXL

Forum statistics

Threads
1,214,599
Messages
6,120,449
Members
448,966
Latest member
DannyC96

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