Rearrange various tables

Posted by lex on September 15, 2000 7:24 AM

I have various reporting tables in formats such as
Jan Feb Mar Apr
AREA1 1 2 3 4
AREA2 2 4 5 6
AREA3 5 6 7 8

which I need to convert to the format
Area1 Jan 1
Area1 Feb 2
Area1 Mar 3
Area1 Apr 4
Area2 Jan 2
Area2 Feb 4

etc.

Any ideas? Even with variations on Vlookup, IndexMatch , Transpose etc I have fallen over on this one so any help would be very helpful. - many thanks!

Posted by Tim Francis-Wright on September 15, 0100 9:01 AM

Here's a brute force way to go about it:

Set up the following ranges:
Data to be the entire table (including headers)
RowNames to be the first (titles) column
ColNames to be the first (titles) row

Set up your new table with 4 columns:
(let's assume here that you have the 3 x 4 table
above, and that your new table starts at cell A10)

A10 1
A11 2
etc.
A21 12

B10, etc. is
=INDEX(Data,2+INT((A10-1)/(ROWS(Data)-1)),1)

C10, etc. is
=INDEX(Data,1,2+MOD((A10-1),COLUMNS(Data)-1))

D10, etc. is
=INDEX(Data,MATCH(B10,RowNames,FALSE),MATCH(C10,ColNames,FALSE))

HTH