userxyz777
New Member
- Joined
- Apr 12, 2018
- Messages
- 15
Hello
I need a vba solution to reverse pivot a pivoted range
There is a simaler question here
https://www.mrexcel.com/forum/excel-questions/1101448-un-pivoting-pivoted-range.html
But it gives a Power Pivot (I think) solution to this question. But need a VBA method.
I am just going to use his example as it covers my needs quite well
Have
Need
As the original OP I have the same issue:
"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 made a Google sheet with the data above
https://docs.google.com/spreadsheets/d/1OYmp1TMLoOZ29_fCf4fBAHgMoPTWVPGU9JmqZZotiCc/edit?usp=sharing
Thanks for help on this
I need a vba solution to reverse pivot a pivoted range
There is a simaler question here
https://www.mrexcel.com/forum/excel-questions/1101448-un-pivoting-pivoted-range.html
But it gives a Power Pivot (I think) solution to this question. But need a VBA method.
I am just going to use his example as it covers my needs quite well
Have
Code:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]Id[/TD]
[TD]Name From[/TD]
[TD]Name To[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alisa Morales[/TD]
[TD]Elwood Iglesias[/TD]
[TD]value two[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Elwood Iglesias[/TD]
[TD]Alisa Morales[/TD]
[TD]value one[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Elwood Iglesias[/TD]
[TD]Georgette Harrison[/TD]
[TD]value three[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Elwood Iglesias[/TD]
[TD]Fannie Fuentes[/TD]
[TD]value four[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fannie Fuentes[/TD]
[TD]Alisa Morales[/TD]
[TD]value three[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fannie Fuentes[/TD]
[TD]Georgette Harrison[/TD]
[TD]Value five[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Georgette Harrison[/TD]
[TD]Alisa Morales[/TD]
[TD]value four[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Georgette Harrison[/TD]
[TD]Elwood Iglesias[/TD]
[TD]value two[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Georgette Harrison[/TD]
[TD]Fannie Fuentes[/TD]
[TD]Value five[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sam Spade[/TD]
[TD]Fannie Fuentes[/TD]
[TD]value one[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sam Spade[/TD]
[TD]Elwood Iglesias[/TD]
[TD]value two[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sam Spade[/TD]
[TD]Alisa Morales[/TD]
[TD]value two[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sam Spade[/TD]
[TD]Georgette Harrison[/TD]
[TD]value four[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sam Spade[/TD]
[TD]Maxy Max[/TD]
[TD]value five[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Maxy Max[/TD]
[TD]Sam Spade[/TD]
[TD]value five[/TD]
[/TR]
</tbody>[/TABLE]
Need
Code:
[TABLE="class: cms_table, width: 0"]
<tbody>[TR]
[TD][TABLE="width: 1307"]
<tbody>[TR]
[TD]Id[/TD]
[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]1[/TD]
[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]2[/TD]
[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]3[/TD]
[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]4[/TD]
[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]5[/TD]
[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]6[/TD]
[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]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As the original OP I have the same issue:
"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 made a Google sheet with the data above
https://docs.google.com/spreadsheets/d/1OYmp1TMLoOZ29_fCf4fBAHgMoPTWVPGU9JmqZZotiCc/edit?usp=sharing
Thanks for help on this