Multi level cross-table to column list

[rafal]

Hi

I wonder if someone can help with a macro for my problem. I have a large set of data in a table that has 3 dimensions across the top (C3:HB5) by 2 dimensions on the side (A6:B515) and all the values in the cross tabulation of the 5 dimensions (C6:HB515). I would like to end up with a flat table of 6 columns that comprise the 5 dimensions and the Value, so that I can then convert and play with it as a pivot table.

Any help would be very welcome. Perhaps there are plug-ins out there that could do this kind of thing?

Thanks
Rafal

 

[rafal]

Okay, I was impatient and think I've worked it out myself. If anyone is interested here's what I did. I don't know if it's the most efficient or clearest method but is now running and appears to be doing the job. R

Sub crosstabtocolumns()
c_paste = 0

'going down each row
For r = 6 To 515

'going across columns in a row

For c = 0 To 208
'dimension1 from row
Sheets("Combined%").Select
Cells(r, 1).Select
Selection.Copy
Sheets("Combined Col").Select
Cells(r + c + c_paste, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'dimension2 from row
Sheets("Combined%").Select
Cells(r, 2).Select
Selection.Copy
Sheets("Combined Col").Select
Cells(r + c + c_paste, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'dimension3 from column
Sheets("Combined%").Select
Cells(3, c + 3).Select
Selection.Copy
Sheets("Combined Col").Select
Cells(r + c + c_paste, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'dimension4 from column
Sheets("Combined%").Select
Cells(4, c + 3).Select
Selection.Copy
Sheets("Combined Col").Select
Cells(r + c + c_paste, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'dimension5 from column
Sheets("Combined%").Select
Cells(5, c + 3).Select
Selection.Copy
Sheets("Combined Col").Select
Cells(r + c + c_paste, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'value
Sheets("Combined%").Select
Cells(r, c + 3).Select
Selection.Copy
Sheets("Combined Col").Select
Cells(r + c + c_paste, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next c

c_paste = c_paste + 210

Next r

End Sub