Excel - Random number generation - with no repeats

Asked By Rya on 08-May-08 09:21 AM
I want to generate a list of random numbers (36,000 of them) such that no two
are alike.  I know how to do the random part [=RAND()*(b-a)+a] for random
numbers between a and b.  Do you know how to ensure no two are alike?

I'm currently using the following formula

=ROUND(RAND()*(36000-0)+0,0)




Joe replied on 08-May-08 09:26 AM
Have two columns.  First column Contains the random number.  Next column has
the numbers 1 to 36,000.  Then sort by the random numbers.  The number 1 to
36,000 wil be now be random.
GarysStuden replied on 08-May-08 09:35 AM
In A1 thru A36000 enter 1 thru 36000.  In B1 thru B36000 enter:

=RAND()

Sort cols A & B by col B.  This will "shuffle" the values in column A.  If
you want 5 sample with no repeats, just pick the top 5 in column A.  If you
want 10 samples with no repeats, just pick the top 10 in column A.......etc.
--
Gary''s Student - gsnu200784
Jim Cone replied on 08-May-08 10:40 AM
The free "Special Randoms" workbook will do that.
Enter minimum and maximum numbers allowed and set size.
Numbers added to a new workbook - copy and paste where you want.
No registration required.  Download from the Products page at my website ...
http://www.realezsites.com/bus/primitivesoftware
--
Jim Cone
Portland, Oregon  USA
(Excel Add-ins / Excel Programming)



I want to generate a list of random numbers (36,000 of them) such that no two
are alike.  I know how to do the random part [=RAND()*(b-a)+a] for random
numbers between a and b.  Do you know how to ensure no two are alike?
I'm currently using the following formula

=ROUND(RAND()*(36000-0)+0,0)