transpose

[wildman]

I would like to know if there is a formula to transpose 4 lines at a
time...
column A
1
2
3
4
5
6
7
8
9
10
11
12

transpose to
a b c d
1 2 3 4
5 6 7 8
9 10 11 12

is this possible?

Thanks in advance
Wildman

 

[jr]

here's a way for trasnposing 20 values contained in
A1:A20 to columns C, D, E and F.

Make a helper array as follows:
g h i j
1 0 1 2 3
2 4 5 6 7
3 8 9 10 11
4 12 13 14 15
5 16 17 18 19

C1=INDIRECT("A"&ROW()+G1)
D1=INDIRECT("A"&ROW()+H1)
E1=INDIRECT("A"&ROW()+I1)
F1=INDIRECT("A"&ROW()+J1)

copy C1:F1 into rows 2, 3, 4 and 5

 

[Gord Dibben]

Wildman

In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1)

The 3000 is an arbitrary number. Your range may differ.

Drag/copy across to E1 then down Columns B:E as far as you need until zeros
start to show up.

Gord Dibben Excel MVP XL2002