MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Random Numbers

Posted by Travis on January 28, 2002 9:59 AM

I'm trying to create a list of 10 random numbers from 1 to 10 without repeating any.
I am using the formula =ROUND(RAND()*10,0) to generate the numbers in each row, but I get several repeated values.
Is there a macro that could be written to check for duplicate values in the 10 cells, and if a duplicate is found then regenerate all the 10 numbers...looping until there are no duplicates.

Its going to be used for a super bowl pool. I realize I could draw out of a hat...but come on...thats no fun.

Posted by Mark W. on January 28, 2002 10:02 AM

Just create the series 1 to 10 in cells A1:A10.
In cells B1:B10 enter the formula, =RAND(). Sort
this 2 column list using the random values in
column B. This will randomize the values in
column A.

Posted by Juan Pablo G. on January 28, 2002 11:17 AM

Another option

In A1:A10 put =RAND()

In B1:B10 put =RANK(A1,$A$1:$A$10)

Juan Pablo G.

Posted by Mark W. on January 28, 2002 12:09 PM

Yeah, the main difference between these 2 approaches
is that the 1st won't produce a new list each and
every time the worksheet is recalculated. =RAND()
is a volatile function.