Hi! I know there is a way to do this but I cannot remember how to do it....

I know I did it in the past and can't for the life of me remeber how I
accomplished it.

I'm using Excel 2003. I have a long column of numbers. I want to insert 4
rows after each number and then copy that number into each new row.

Any thoughts how to accomplish this automatically?

Denise

Hi,

If your data are in Column A (say A2 to A101), and if there is nothing else
in that worksheet, use the following formula in B2 and drag the column down
to 5 times the number of rows in Column A.

=INDIRECT("$A$"&QUOTIENT(ROW()-2,5)+2)

(Here the "2" following the "-" sign is the row number where the new column
would start, and the "2" following the "+" sign is the row number of the
first value in Column A. Modify those numbers appropriately.

After creating column B, select and copy the data in that column, "Edit"--
"Paste Special" -- check "Values" -- "OK". This makes Column B to be
independent of Column A; you can now delete Column A.

If, on the other hand, you want to create a new worksheet where each number
repeats in 5 successive rows, use a slightly different version of the above
formula in B2 of the new sheet.

=INDIRECT("Sheet1!$A$"&QUOTIENT(ROW()-2,5)+2)

Hope this helps,

Regards,
B. R. Ramachandran



"Denise" wrote:

Hi! I know there is a way to do this but I cannot remember how to do it....

I know I did it in the past and can't for the life of me remeber how I
accomplished it.

I'm using Excel 2003. I have a long column of numbers. I want to insert 4
rows after each number and then copy that number into each new row.

Any thoughts how to accomplish this automatically?

Denise