Rearrange certain columns and rows

Question

I have an Excel spreadsheet formatted like this:

What I have been trying to do is format it to look like this:

So it's kind of transposed I guess (not sure how to call it).

I've spent the last hour and a half trying to do it in VBA with no success.

This is just a sample of how it is formatted, in reality there's about 50,000 of these, so I need to do it using VBA or something of the sort.

Would someone be able to help me out with how to do this?

Answer 1

With Excel 2007 you don’t necessarily need VBA. In Pivot Table Wizard (Alt+D, P) select ‘Multiple consolidation ranges’, Next, select ‘I will create the page fields’, Next, select your data, Next, select ‘New worksheet’, Finish. Double click on bottom RH cell of pivot table. Filter on ColumnA and delete blank rows, filter on ColumnB and delete rows containing “Type". Insert columns to the right of “Row” and “Column” and fill with lookup values.