Need to convert Columns to Rows and nest within the current rows

[Glaceon]

Hi!

Here's a link to my data: https://app.box.com/MyQuestion

My Question:

In the file that I'm sharing (link above), my data is arranged like this:
[TABLE="width: 519"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]R1[/TD]
[TD]Customer[/TD]
[TD]39222[/TD]
[TD]33525[/TD]
[TD]34035[/TD]
[TD]34136[/TD]
[TD]34712[/TD]
[/TR]
[TR]
[TD]R2[/TD]
[TD="align: right"]5440226048[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]R3[/TD]
[TD="align: right"]5440270028[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]R4[/TD]
[TD="align: right"]1018109100[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Rows are "customers", columns, are product codes and in the intersection are the qty purchased.

What I'd like to accomplish is to "nest" within each customer the respective product codes and then the next column being the respective quantities.
Essentially, take the 80+ columns of data, and "nest" them within each of the rows.


Sample outcome:

[TABLE="width: 322"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]R1[/TD]
[TD]Customer[/TD]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]R2[/TD]
[TD="align: right"]5440226048[/TD]
[TD][/TD]
[TD]39222[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R3[/TD]
[TD]5440226048[/TD]
[TD][/TD]
[TD]33525[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]R4[/TD]
[TD]5440226048[/TD]
[TD][/TD]
[TD]34035[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]R5[/TD]
[TD]5440226048[/TD]
[TD][/TD]
[TD]34136[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]R6[/TD]
[TD]5440226048[/TD]
[TD][/TD]
[TD]34712[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]R7[/TD]
[TD]5440270028[/TD]
[TD][/TD]
[TD]39222[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]R8[/TD]
[TD]5440270028[/TD]
[TD][/TD]
[TD]33525[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]R9[/TD]
[TD]5440270028[/TD]
[TD][/TD]
[TD]34035[/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]R10[/TD]
[TD]5440270028[/TD]
[TD][/TD]
[TD]34136[/TD]
[TD][/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]R11[/TD]
[TD]5440270028[/TD]
[TD][/TD]
[TD]34712[/TD]
[TD][/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]R12[/TD]
[TD]1018109100[/TD]
[TD][/TD]
[TD]39222[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]R13[/TD]
[TD]1018109100[/TD]
[TD][/TD]
[TD]33525[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]R14[/TD]
[TD]1018109100[/TD]
[TD][/TD]
[TD]34035[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]R15[/TD]
[TD]1018109100[/TD]
[TD][/TD]
[TD]34136[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]R16[/TD]
[TD]1018109100[/TD]
[TD][/TD]
[TD]34712[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[XLRATOR]

Hello Glaceon,
The following code transposes your data by transferring it to Worksheet called "Sheet3":

Dim Rng As Range, i As Integer
For i = 2 To 82
For Each Rng In Range("A2:A10000").Cells
If Rng.Value <> "" Then
If Cells(Rng.Row, i).Value <> "" Then
Sheets("Sheet3").Range("A1048576").End(xlUp).Offset(1, 0).Value = Rng.Value
Sheets("Sheet3").Range("B1048576").End(xlUp).Offset(1, 0).Value = Cells(1, i).Value
Sheets("Sheet3").Range("C1048576").End(xlUp).Offset(1, 0).Value = Cells(Rng.Row, i).Value
End If
End If
Next
Next
MsgBox "Done!"

Put this code in a button in your datasource sheet and give it a try.
Good luck.

 

[Glaceon]

Hello Glaceon,
The following code transposes your data by transferring it to Worksheet called "Sheet3":

Dim Rng As Range, i As Integer
For i = 2 To 82
For Each Rng In Range("A2:A10000").Cells
If Rng.Value <> "" Then
If Cells(Rng.Row, i).Value <> "" Then
Sheets("Sheet3").Range("A1048576").End(xlUp).Offset(1, 0).Value = Rng.Value
Sheets("Sheet3").Range("B1048576").End(xlUp).Offset(1, 0).Value = Cells(1, i).Value
Sheets("Sheet3").Range("C1048576").End(xlUp).Offset(1, 0).Value = Cells(Rng.Row, i).Value
End If
End If
Next
Next
MsgBox "Done!"

Put this code in a button in your datasource sheet and give it a try.
Good luck.

XLRATOR -

THANK YOU! THANK YOU! THANK YOU!

Sincerely, you bailed me out of a tight spot yesterday!
Hope the universe smiles kindly upon you.

 

[XLRATOR]

Glad that Helped.
And thanks for your wishes.

Regards.