Changing columns to rows 1

[sssu]

Hi...Help please.

Say I have a matrix that has dates as coloumns and company names as rows ie

1994 1995 1996
A 1 5 3
B 1 3 7

I want to change the layout so i only have three columns

A 1994 1
A 1995 5
A 1996 3
B 1994 1
etc

Can anyone suggest anything?

Thanks and much appreciated.

 

[GlennUK]

Does your data only have 3 dates?

Glenn.

 

[sssu]

No it has many dates.
Basically, it is monthly data beginning in 1994 and ending in 2003.
For eg 199401 199402 199402 199403 etc

 

[GlennUK]

OK, these formulae work if entered in row 1 ( any column ), and then copied down as required. They assume that you have the dates in row 1, and that the companies and figures start in row 2.

To retrieve company use:
=INDEX(A:A,INT((ROW()-1)/numdates)+2)

To retrieve appropriate date use:
=INDEX($B$1:$G$1,MOD(ROW()-1,numdates)+1)

To retrieve data use:
=INDEX($1:$65536,INT((ROW()-1)/numdates)+2,MOD(ROW()-1,numdates)+1+1)

I've used numdates to represent the number of columns that your dates cover, so replace that with the correct number.

Good luck.

Cheers, Glenn.