How can I create a list of random numbers with no duplicates?

K

Kwasniewski

Anyone know how to generate a list of random numbers (I've been using
=RANDBETWEEN (X,Y) ) that does not include any duplicates?
 
R

Ragdyer

If you wish, you can create a random, without replacement list, without
sorting or using code.

In an out-of-the-way area of your sheet, say Column Z, enter the Rand()
function as far down as the amount of numbers you wish to draw from.

If 50 numbers, 1 to 50, enter
=RAND()
in Z1, and copy down to Z50.

Enter this formula, and copy down as many rows as you would want random
numbers to display:

=INDEX(ROW($1:$50),RANK(Z1,$Z$1:$Z$50))

Now, a *new* set of random numbers will display with each hit of <F9>.

Also, to change the numbers to draw from, just change the row numbers,
keeping in mind that you choose a set of 50.

For 101 to 150, use:

=INDEX(ROW($101:$150),RANK(Z1,$Z$1:$Z$50))
 
Top