How do I invert matrices?

[Guest]

How can I make a matrix that looks like this
Jan Feb Mar
95 A B C
96 D E F
97 G H I

look like this

95 Jan A
95 Feb B
95 Mar C
96 Jan D
....

 

[AlfD]

Hi!

Before getting near this one I would like to know the real shape o
your data. I'm sure it is not just 3 months from 3 years.

Presumably it has 12 months and N years?


Al

 

[AlfD]

Hi again!

A thought crossed my mind so I pursued it.

I assumed you have a table with years 95 to 99 as row headings in col
and months Jan to Dec as col headings in row 1.

Give this a name, say Table1.

In clear space below your table, say in rows 11 to 13:
set B11 =95+INT((COLUMN()-1.1)/12)
set B12:M12 as months of the year (Jan to Dec)
set B13 =HLOOKUP(B12,Table1,B11-93,FALSE)

Copy these across to Col BI (i.e. to Dec 99).

Row 13 should now be showing your data from Table1.

Finish off by copying A11:BI13. Paste Special, checking both Values an
transpose and put where you want it.

Seems to work for me.

Could be further tidied by linking the month names in row 12 to th
headings in Table1, which would help to avoud some kinds of typos.

I leave it to you to deal with 2000 onward if they arise!

Al

 

[Harlan Grove]

How can I make a matrix that looks like this
Jan Feb Mar
95 A B C
96 D E F
97 G H I

look like this

95 Jan A
95 Feb B
95 Mar C
96 Jan D
...

Normalizing a crosstab. If the original table were named TBL and the top-left
result cell were X1, try

X1:
=INDEX(TBL,2+INT((ROW()-ROW($X$1))/(COLUMNS(TBL)-1)),1)

Y1:
=INDEX(TBL,1,2+MOD(ROW()-ROW($X$1),COLUMNS(TBL)-1))

Z1:
=INDEX(TBL,2+INT((ROW()-ROW($X$1))/(COLUMNS(TBL)-1)),
2+MOD(ROW()-ROW($X$1),COLUMNS(TBL)-1))

Select X1:Z1 and fill down as far as needed. Columna A and C formulas return
#REF! errors when the data in TBL has been ehausted.