I have the following formula in Excel (not vba):
Then I auto fill down 10 rows. How can I re-write the formula so the numbers don't repeat.
migrated from stackoverflow.com Apr 30 '11 at 11:50
This question came from our site for professional and enthusiast programmers.
There is a way to do it with worksheet functions only. However, you can't do it neatly in 10 cells. Instead you need 10 columns of ten cells. Do the following:
J1:J10 will be a random sequence of the numbers 1 to 10.
EDIT (years after the fact...): There is a much easier way to do this with two columns. In the first column, enter
and fill down as far as you need.
In the adjacent column, enter
and fill down. There is a negligible chance that of the 10 random floating point decimals (15 digits accuracy, I think), any two numbers will be the same. Thus,
This previous answer does something similar: see how
But it requires using VBA. I don't know if you want that or if you insist on using worksheet formulas only.