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:

Order	Material	SizeA1	SizeA2	SizeB1	SizeB2	SizeC1	SizeC2	Doz1	Doz2
1234	MAT1	30	31	A	AA	GM	GN	24	63
4321	MAT2	40	44	XL	4X	MN	MO	524	636
5443	MAT3	50		3XL		QQ		2516

<tbody>
</tbody>

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:

Order	Material	Size	Doz
1234	MAT1	30	24
1234	MAT1	31	63
1234	MAT1	A	24
1234	MAT1	AA	63
1234	MAT1	GM	24
1234	MAT1	GN	63
4321	MAT2	40	524
4321	MAT2	44	636
4321	MAT2	XL	524
4321	MAT2	4X	636
4321	MAT2	MN	524
4321	MAT2	MO	636
5443	MAT3	50	2516
5443	MAT3	3XL	2516
5443	MAT3	QQ	2516

<tbody>
</tbody>

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;