How to go from table to rows??

  • Thread starter Thread starter Anders
  • Start date Start date
A

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
 
Assume source data as posted is in Sheet1, where
in A2 down are the numbers: 1,2,3..
in B1:D1 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
 
Back
Top