I want to create unique randomn numbers

C

charlieking4747

I want to create 55,000 randomised numbers between 10,001 and 65,000. Each
number must appear only once. Is this possible?
 
B

Bob Phillips

First, ensure cell B1 is empty and goto Tools>Options and on the
Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell A1

=IF(($B$1="")+(AND(A1>=0,COUNTIF(A$1:A$55000,A1)=1)),A1,RANDBETWEEN(10001,65
000)

it should show a 0

Copy A1 down to A55000.

Finally, put some value in B1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, edit cell A1, don't change it, just edit to
recalculate,
copy A1 down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
C

charlieking4747

Dear Bob,
Thanks for the swift reply, I have tried this and it reports i have a
parenthesis missing, also what number should i enter in the number of
iterations box?
Thanks again
Regards Charlie King
 
B

Bob Phillips

It might be NG wrap-around. Try

=IF(($B$1="")+(AND(A1>=0,COUNTIF(A$1:A$55000,A1)=1)),A1,
RANDBETWEEN(10001,65000)

Leave the number of iterations at the default. You might need to up it with
that number of rows, didn't test with that many.

You can check it by adding this

=SUMPRODUCT((A10001:A65000<>"")/COUNTIF(A10001:A65000,A10001:A65000&""))

it should say 55000

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

Be aware, it is going to be extremely slow with 55000 numbers.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top