# Non identical random numbers

I have the following formula in Excel (not vba):

``````=RANDBETWEEN(1,10)
``````

Then I auto fill down 10 rows. How can I re-write the formula so the numbers don't repeat.

-

## migrated from stackoverflow.comApr 30 '11 at 11:50

This question came from our site for professional and enthusiast programmers.

You can't. A random number means you can't predict what the next one will be - a random number in such a tight range (1-10) is likely to have a repeated value. This question is off-topic here, anyway, since it's "(not VBA)". Voting to move to superusers – Ken White Apr 29 '11 at 17:01
You may actually want a random sort? If you're generating the numbers randomly, you could get the same value multiple times, in a sample of this size. Or, you could make a check to see what numbers have already been selected; I think you'd need a macro for that (VBA?). – Piskvor Apr 29 '11 at 17:02
You may as well use "CELL" and get the rownumber for each row if you want to have 10 unique numbers over 10 rows... – gbn Apr 29 '11 at 17:04
@Ken, I agree that this question should be move to SuperUser, but realize that it doesn't have to be VBA to be here on SO. You can have questions on worksheet-functions that are appropriate. – Lance Roberts Apr 29 '11 at 17:09
@Ken, Excel worksheet-functions are very often programming-syntax and development problems. They can get quite complex. – Lance Roberts Apr 29 '11 at 17:34

There is a an easy way to do this with two columns. In A1, enter

`````` =RAND()
``````

and fill down to A10.

``````=RANK(A1,\$A\$1:\$A\$10)
``````

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, `RANK` will effectively always generate a random ordering of values from 1 to 10.

-
Since you are, in effect, using 10 random seeds (in `A1:A10`) to generate 10 numbers, the result should be just as random. Of course, the stipulation that no number is repeated in the final sequence reduces the randomness, to begin with. +1 – kaloyan Apr 29 '11 at 21:27

This previous answer does something similar: see how `idx` array is filled in the second example.

But it requires using VBA. I don't know if you want that or if you insist on using worksheet formulas only.

-