Hello, I have data in the shape
I need to UN-pivot it so it becomes
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
Thank you
Here is a Google sheet with this data
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
Thank you
Here is a Google sheet with this data