Reverse pivot a pivoted range (Column to Matrix) using VBA

[userxyz777]

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

<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

[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

 

[mrshl9898]

Not quite following the results.

type 3 on id 5 is stated as expecting value one with data showing value 2
why does the second last line not continue on id5? Does id equal the name? If 6 is correct then why is that Maxy Max? And why are relationship and type 1 missing?

I was thinking something like this... But I can't understand the logic behind the result.

Sub unpivot()


Dim rownum As Long
Dim rownum2 As Long
Dim colnum As Long


rownum = 2
rownum2 = 2
colnum = 7
counter = 1


Cells(rownum2, colnum).Value = Cells(rownum, 1).Value
Cells(rownum2, colnum + 1).Value = Cells(rownum, 2).Value
Cells(rownum2, colnum + 2).Value = Cells(rownum, 3).Value
Cells(rownum2, colnum + 3).Value = Cells(rownum, 4).Value
rownum = rownum + 1




Do Until Cells(rownum, 1).Value = ""
    If Cells(rownum, 1).Value = Cells(rownum - 1, 1).Value Then
    colnum = colnum + 2
    Cells(rownum2, colnum + 2).Value = Cells(rownum, 3).Value
    Cells(rownum2, colnum + 3).Value = Cells(rownum, 4).Value
    Else
    rownum2 = rownum2 + 1
    colnum = 7
    Cells(rownum2, colnum).Value = Cells(rownum, 1).Value
    Cells(rownum2, colnum + 1).Value = Cells(rownum, 2).Value
    Cells(rownum2, colnum + 2).Value = Cells(rownum, 3).Value
    Cells(rownum2, colnum + 3).Value = Cells(rownum, 4).Value
    End If
    rownum = rownum + 1
Loop




End Sub