Transposing multiple rows into one column, but with offset

[JensT]

I need to to change e.g.:

1 2 3 4

5 6 7 8


into:

1

5

2

6

3

7

4

8


And I need to do this with 500 columns. I have no experience doing this in excel so would very much appreciate getting a code that can do this.

 

[Haseeb A]

Hello JensT,


Assuming the data is on Sheet1 & starting from A2 to down & across. also you are on Excel 2007 or later, since you have >256 columns


In a new sheet eg: Sheet2!A1 enter


=COUNTA(Sheet1!A2:A100000) `this will give how many rows have data (assuming there are no blank cells between data)


B1,


=A1*COUNTA(Sheet1!A2:WZ2) `this will give the count of total data (assuming there are no blank cells between A2:WZ2)


Then in A3 and copy down as needed.


=IF(ROWS(A$3:A3)<=B$1,INDEX(Sheet1!$A$2:$WZ$100000,MOD(ROWS(A$3:A3)-1,A$1)+1,INT((ROWS(A$3:A3)-1)/A$1)+1),"")

 

[NARAYANK991]

Hi ,


Do you think this link is helpful ?


http://www.cpearson.com/excel/MatrixToVector.aspx


Narayan