I want to create unique randomn numbers
 

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

I want to create unique randomn numbers
 

First, ensure cell B1 is empty and goto ToolsOptions 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)

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

I want to create unique randomn numbers
 

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

"Bob Phillips" wrote:

First, ensure cell B1 is empty and goto ToolsOptions 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)

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

I want to create unique randomn numbers
 

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)

"charlieking4747" wrote in
message ...
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

"Bob Phillips" wrote:

First, ensure cell B1 is empty and goto ToolsOptions 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)

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

I want to create unique randomn numbers
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
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)

"charlieking4747" wrote in
message ...
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

"Bob Phillips" wrote:

First, ensure cell B1 is empty and goto ToolsOptions 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)

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

I want to create unique randomn numbers
 

Hello Charlie,

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

HTH,
Bernd