How to go from table to rows??

[Anders]

Hi,

I have a problem changing the setup of my data in order to merge it into
another dataset.

Now I have data like this:

Person\Year
1981 1982 1983
1 12 22 11
2 8 34 1
3 42 9 14

What I want is data like this:

Person year
1 1981 12
1 1982 22
1 1983 11
Etc...

That is go from matrix to column based.
Can anyone help???

Best
Anders

 

[Max]

Assume source data as posted is in Sheet1, where
in A2 down are the numbers: 1,2,3..
in B11 are the years: 1981, 1982, 1983

In another sheet,
In A2: =OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/3),)
In B2: =INDEX(Sheet1!$B$1:$D$1,MOD(ROWS($1:1)-1,3)+1)
In C2: =OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Select A2:C2, copy down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik