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?

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)

"charlieking4747" <[email protected]> wrote in
message news:[email protected]...
> I want to create 55,000 randomised numbers between 10,001 and 65,000. Each
> 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

"Bob Phillips" wrote:

> 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)
>
> "charlieking4747" <[email protected]> wrote in
> message news:[email protected]...
> > I want to create 55,000 randomised numbers between 10,001 and 65,000. Each
> > number must appear only once. Is this possible?
>
>
>

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" <[email protected]> wrote in
message news:[email protected]...
> 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 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)
> >
> > "charlieking4747" <[email protected]> wrote in
> > message news:[email protected]...
> > > I want to create 55,000 randomised numbers between 10,001 and 65,000.
Each
> > > number must appear only once. Is this possible?
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
> > 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 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)
> > >
> > > "charlieking4747" <[email protected]> wrote in
> > > message news:[email protected]...
> > > > I want to create 55,000 randomised numbers between 10,001 and
65,000.
> Each
> > > > number must appear only once. Is this possible?
> > >
> > >
> > >
>
>

Hello Charlie,

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

HTH,
Bernd