Excel 2002: Any formula to tranpose columns into rows ?

[Guest]

Dear Sir

Lets consider the following worksheet:

A B C D

1 12 26 78 88
2 21 14 65 52
3 32 61 42 31



7 12
8 21
9 32
10 26
11 14
12 61
13 78
14 65
15 42
16 88
17 52
18 31


May I know if you have a formula to tranpose content in cell A1: D3 to a
single Column A ( A7: A18) as ilustrated above ?

Any formula to input at cell A7 and copy down to get the straight answer ?

Thanks

Low

 

[T. Valko]

Try this:

=OFFSET(A$1,MOD(ROWS(A$1:A1)-1,n),INT((ROWS(A$1:A1)-1)/n))

Where n = number of rows in your table. In your case n = 3

Biff

 

[Pete_UK]

You could try this in A7 and copy it down to A18:

=INDEX(A$1$3,MOD(ROW()-1,3)+1,INT((ROW()-1)/3)-1)

Hope this helps.

Pete

 

[Guest]

Hello T. Valko,

Great formula. It works perfectly well.

Many thanks

Low

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Hello T. Valko,

Great formula. It works perfectly well.

Many thanks

Low

 

[Guest]

Dear Pete,

I do not have any problem in using
=INDEX(A$1$3,MOD(ROW()-1,3)+1,INT((ROW()-1)/3)-1)
to get the correct answer from 4 column X 3 rows table earlier on.

However when I modify the formula to
=INDEX(A$1$12,MOD(ROW()-1,12)+1,INT((ROW()-1)/12)-1)

for the 4 column x 12 rows table, I could not get the answer right.
May I know why ?

llustrated example:

A B C
D
1 12.00 26.00 78.00
88.00
2 21.00 14.00 65.00
52.00
3 32.00 61.00 42.00
32.00
4 66.00 70.00 22.00
10.00
5 31.00 41.00 21.00
9.00
6 10.00 33.00 30.00
6.00
7 44.00 54.00 8.00
46.00
8 38.00 10.00 7.00
64.00
9 77.00 92.00 13.00
52.00
10 99.00 22.00 31.00
25.00
11 64.00 11.00 43.00
31.00
12 55.00 9.00 39.00
80.00

Transposed data
19 44.00
20 38.00
21 77.00
22 99.00
23 64.00
24 55.00
25 12.00
26 21.00
27 32.00
28 66.00
29 31.00
30 10.00

Thanks


Low