Reverse Pivot-type Data (that's not a pivot table)



Data is not in an actual pivot table (so it can't be reversed to get what I
want), it's just entered in a crosstab format:

June July August
Andy 2 3 4
Dave 3 6 2

Any easy way to convert it to this format?

Name Month Sales
Andy June 2
Andy July 3
Andy August 4
Dave June 3
Dave July 6
Dave August 2



Assuming source table below is in A1:D3
June July August
Andy 2 3 4
Dave 3 6 2

In F2: =OFFSET($A$2,INT((ROWS($1:1)-1)/3),)
In G2: =INDEX($B$1:$D$1,MOD(ROWS($1:1)-1,3)+1)
In H2: =OFFSET($B$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy F2:H2 down to H7 to extract desired results
Adapt to suit .. eg: the "3" = the number of "month" cols in the source


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question