VBA - Modify table data in a transpose-y way

[Zenru]

Hi, I have a horrible table with information which was left to me. A bit of the legacy the previous dude left. The table goes like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Material[/TD]
[TD]SizeA1[/TD]
[TD]SizeA2[/TD]
[TD]SizeB1[/TD]
[TD]SizeB2[/TD]
[TD]SizeC1[/TD]
[TD]SizeC2[/TD]
[TD]Doz1[/TD]
[TD]Doz2[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]MAT1[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]A[/TD]
[TD]AA[/TD]
[TD]GM[/TD]
[TD]GN[/TD]
[TD]24[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]MAT2[/TD]
[TD]40[/TD]
[TD]44[/TD]
[TD]XL[/TD]
[TD]4X[/TD]
[TD]MN[/TD]
[TD]MO[/TD]
[TD]524[/TD]
[TD]636[/TD]
[/TR]
[TR]
[TD]5443[/TD]
[TD]MAT3[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]3XL[/TD]
[TD][/TD]
[TD]QQ[/TD]
[TD][/TD]
[TD]2516[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

It is a table that has some orders of some materials and their sizes. Each order and material can have up to 20 sizes. So, there really should be SizeA1 to SizeA20, SizeB1 to Size B20 and SizeC1 to SizeC20 and Doz1 to Doz20.

In reality SizeA1 to 20 are the main sizes and SizeB and SizeC are just different versions for different processes. Just when you see different size codes for shoes in different countries but they all mean the same.

The way this data is ordered makes it to hard to read and work with, so I need a way to modify it so it becomes like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Material[/TD]
[TD]Size[/TD]
[TD]Doz[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]MAT1[/TD]
[TD]30[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]MAT1[/TD]
[TD]31[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]MAT1[/TD]
[TD]A[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]MAT1[/TD]
[TD]AA[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]MAT1[/TD]
[TD]GM[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]MAT1[/TD]
[TD]GN[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]MAT2[/TD]
[TD]40[/TD]
[TD]524[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]MAT2[/TD]
[TD]44[/TD]
[TD]636[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]MAT2[/TD]
[TD]XL[/TD]
[TD]524[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]MAT2[/TD]
[TD]4X[/TD]
[TD]636[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]MAT2[/TD]
[TD]MN[/TD]
[TD]524[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]MAT2[/TD]
[TD]MO[/TD]
[TD]636[/TD]
[/TR]
[TR]
[TD]5443[/TD]
[TD]MAT3[/TD]
[TD]50[/TD]
[TD]2516[/TD]
[/TR]
[TR]
[TD]5443[/TD]
[TD]MAT3[/TD]
[TD]3XL[/TD]
[TD]2516[/TD]
[/TR]
[TR]
[TD]5443[/TD]
[TD]MAT3[/TD]
[TD]QQ[/TD]
[TD]2516[/TD]
[/TR]
</tbody>[/TABLE]


So instead of having up to 60 sizes distributed in 60 columns I want them in rows with their respective Doz quantity in 20 columns. Just like that, a monster table of 80+ columns can be simplified in 5 columns. In this new table, we could add as many sizes for an order as we want (since you can see they are limited to 20 , right now it is not a problem but we have future plans to increase the amount of sizes permitted and well, things will get really nasty)

It is worth noticing that not all orders can have all sizes filled. There are are orders, like the 5334 that just have a size in SizeA1, B1 and C1 and thus Doz1 is the only one filled.

How can I approach this in a VBA way? Or can pivots help me?

 

[Zenru]

bump;