Transpose every 10 cells in single column to multiple rows

B

Bernard Liengme

In A1:A100 I have numbers (I used 1,2,3... so I could easily check my
result)

In D1:D10 I have the sequence 1, 11,21... (Just type 1, 11; select both;
pull the fill handle down)

In E1 I have the formula =INDIRECT("A"&$D1+COLUMN()-5)
This evaluates to =INDIRECT("A1") in that cell; The 5 is these because I
started in column E
Copied this across and down to fill E1:M10
Now I have
1,2,3..10
11,12,13..20
etc

Now I can use Copy followed by Paste Special - Values to convert to real
numbers if I so wish

best wishes
 
T

Teethless mama

In C1: =OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*10,0)

copy from C1 across to L1, while C1:L1 are selected and copy down as far as
needed
 
Top