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

[Guest]

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

 

[Peo Sjoblom]

http://www.mcgimpsey.com/excel/udfs/randint.html



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[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))