Unpivot Excel data

[RRinTetons]

Excel 2007 or 2010

I have a worksheet with standard pivot format data, row headers down the side, column headers across the top and a data value for each row/column intersection. I need to turn that into a columnwise format.

e.g.

col1 col2 col3
row1 r1c1val r1c2val r1c3val
row2 r2c1val r2c2val r2c3val
row3 r3c1val r3c2val r3c3val

needs to become

row1 col1 r1c1val
row1 col2 r1c2val
row1 col3 r1c3val
row2 col1 r2c1val
row2 col2 r2c2val
row2 col3 r2c3val
row3 col1 r3c1val
row3 col2 r3c2val
row3 col3 r3c3val

Eventually, I want a user to select the column headings and name the range, then the row headings and name the range and then the values and name the range, then create a new worksheet in the workbook and populate it.

For now, I'd settle for just figuring out the first step!

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Hi,

faq68-5287

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

Not bad! I can certainly use that one in general.

Regrettably, it's beyond the users for this worksheet. Once I have the data normalized I then need to generate SQL insert statements and execute those.

I think I've got some traction using arrays, but I need to figure out how to get from a named range to an array in my vba code. Any hints on that one?


-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

You can do a multiple UNION query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}