Transpose every 10 cells in single column to multiple rows
 

a column is 30,000 cells long, I need to copy every 10 cells into rows.

Transpose every 10 cells in single column to multiple rows
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"nicktjr" wrote in message
...
a column is 30,000 cells long, I need to copy every 10 cells into rows.

Transpose every 10 cells in single column to multiple rows
 

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


"nicktjr" wrote:

a column is 30,000 cells long, I need to copy every 10 cells into rows.