software

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

First, ensure cell B1 is empty and goto Toolsgt;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=quot;quot;) (AND(A1gt;=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)

quot;charlieking4747quot; gt; wrote in
message ...
gt; I want to create 55,000 randomised numbers between 10,001 and 65,000. Each
gt; number must appear only once. Is this possible?
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

quot;Bob Phillipsquot; wrote:

gt; First, ensure cell B1 is empty and goto Toolsgt;Options and on the
gt; Calculation
gt; tab check the Iteration checkbox to stop the Circular Reference message.
gt;
gt; Next, type this formula into cell A1
gt;
gt; =IF(($B$1=quot;quot;) (AND(A1gt;=0,COUNTIF(A$1:A$55000,A1)=1 )),A1,RANDBETWEEN(10001,65
gt; 000)
gt;
gt; it should show a 0
gt;
gt; Copy A1 down to A55000.
gt;
gt; Finally, put some value in B1, say an 'x', and all the random numbers will
gt; be generated, and they won't change.
gt;
gt; To force a re-calculation, edit cell A1, don't change it, just edit to
gt; recalculate,
gt; copy A1 down.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;charlieking4747quot; gt; wrote in
gt; message ...
gt; gt; I want to create 55,000 randomised numbers between 10,001 and 65,000. Each
gt; gt; number must appear only once. Is this possible?
gt;
gt;
gt;

It might be NG wrap-around. Try

=IF(($B$1=quot;quot;) (AND(A1gt;=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:A65000lt;gt;quot;quot;)/COUNTIF(A10001:A65000,A10001:A65000amp;quot;quot;))

it should say 55000

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;charlieking4747quot; gt; wrote in
message ...
gt; Dear Bob,
gt; Thanks for the swift reply, I have tried this and it reports i have a
gt; parenthesis missing, also what number should i enter in the number of
gt; iterations box?
gt; Thanks again
gt; Regards Charlie King
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; First, ensure cell B1 is empty and goto Toolsgt;Options and on the
gt; gt; Calculation
gt; gt; tab check the Iteration checkbox to stop the Circular Reference message.
gt; gt;
gt; gt; Next, type this formula into cell A1
gt; gt;
gt; gt;
=IF(($B$1=quot;quot;) (AND(A1gt;=0,COUNTIF(A$1:A$55000,A1)=1 )),A1,RANDBETWEEN(10001,65
gt; gt; 000)
gt; gt;
gt; gt; it should show a 0
gt; gt;
gt; gt; Copy A1 down to A55000.
gt; gt;
gt; gt; Finally, put some value in B1, say an 'x', and all the random numbers
will
gt; gt; be generated, and they won't change.
gt; gt;
gt; gt; To force a re-calculation, edit cell A1, don't change it, just edit to
gt; gt; recalculate,
gt; gt; copy A1 down.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;charlieking4747quot; gt; wrote in
gt; gt; message ...
gt; gt; gt; I want to create 55,000 randomised numbers between 10,001 and 65,000.
Each
gt; gt; gt; number must appear only once. Is this possible?
gt; gt;
gt; gt;
gt; gt;
Be aware, it is going to be extremely slow with 55000 numbers.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Bob Phillipsquot; gt; wrote in message
...
gt; It might be NG wrap-around. Try
gt;
gt; =IF(($B$1=quot;quot;) (AND(A1gt;=0,COUNTIF(A$1:A$55000,A1)=1 )),A1,
gt; RANDBETWEEN(10001,65000)
gt;
gt; Leave the number of iterations at the default. You might need to up it
with
gt; that number of rows, didn't test with that many.
gt;
gt; You can check it by adding this
gt;
gt; =SUMPRODUCT((A10001:A65000lt;gt;quot;quot;)/COUNTIF(A10001:A65000,A10001:A65000amp;quot;quot;))
gt;
gt; it should say 55000
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;charlieking4747quot; gt; wrote in
gt; message ...
gt; gt; Dear Bob,
gt; gt; Thanks for the swift reply, I have tried this and it reports i have a
gt; gt; parenthesis missing, also what number should i enter in the number of
gt; gt; iterations box?
gt; gt; Thanks again
gt; gt; Regards Charlie King
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; First, ensure cell B1 is empty and goto Toolsgt;Options and on the
gt; gt; gt; Calculation
gt; gt; gt; tab check the Iteration checkbox to stop the Circular Reference
message.
gt; gt; gt;
gt; gt; gt; Next, type this formula into cell A1
gt; gt; gt;
gt; gt; gt;
gt;
=IF(($B$1=quot;quot;) (AND(A1gt;=0,COUNTIF(A$1:A$55000,A1)=1 )),A1,RANDBETWEEN(10001,65
gt; gt; gt; 000)
gt; gt; gt;
gt; gt; gt; it should show a 0
gt; gt; gt;
gt; gt; gt; Copy A1 down to A55000.
gt; gt; gt;
gt; gt; gt; Finally, put some value in B1, say an 'x', and all the random numbers
gt; will
gt; gt; gt; be generated, and they won't change.
gt; gt; gt;
gt; gt; gt; To force a re-calculation, edit cell A1, don't change it, just edit to
gt; gt; gt; recalculate,
gt; gt; gt; copy A1 down.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;charlieking4747quot; gt; wrote in
gt; gt; gt; message ...
gt; gt; gt; gt; I want to create 55,000 randomised numbers between 10,001 and
65,000.
gt; Each
gt; gt; gt; gt; number must appear only once. Is this possible?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
Hello Charlie,

I suggest to take my UDF UniqRandInt() from www.sulprobil.com

HTH,
Bernd