Stop Number duplication in a range

I have created a Lotto Checker, how can I stop a number from being repeated
in the `winning number` input cell/s

I am already using validation to restrict the input from 1 to 49.

If you can assist please post back to this board.

PLEASE ALWAYS POST BACK TO THE BOARD, OTHEWRS MAY FIND THE SOLUTION HELPFUL

Have a look here an JE McGimpsey's site for a couple of methods.

http://www.mcgimpsey.com/excel/udfs/randint.html


Gord Dibben Excel MVP

On Sun, 20 Mar 2005 20:30:46 -0000, "Nu-bEE" <bertie@basset.com> wrote:

>I have created a Lotto Checker, how can I stop a number from being repeated
>in the `winning number` input cell/s
>
>I am already using validation to restrict the input from 1 to 49.
>
>If you can assist please post back to this board.
>
>PLEASE ALWAYS POST BACK TO THE BOARD, OTHEWRS MAY FIND THE SOLUTION HELPFUL
>

Check out this page of Chip Pearson's.

Scroll down to "D", and see all the goodies he's got there concerning
duplicates.

http://www.cpearson.com/excel/topic.htm
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Nu-bEE" <bertie@basset.com> wrote in message
news:OQkNXuYLFHA.3016@TK2MSFTNGP15.phx.gbl...
> I have created a Lotto Checker, how can I stop a number from being
repeated
> in the `winning number` input cell/s
>
> I am already using validation to restrict the input from 1 to 49.
>
> If you can assist please post back to this board.
>
> PLEASE ALWAYS POST BACK TO THE BOARD, OTHEWRS MAY FIND THE SOLUTION
HELPFUL
>
>

It seems I didn't quite comprehend your question as well as Gord did.

Here's a way to display the random *order* of a set amount of digits, which
in your case would be 1 to 49.
You wouldn't have to sort with this procedure either, just hit the <F9> key
to recalc the sheet and produce a new set of non-repeating numbers.

In A1 enter:
=RAND()
And copy down to A49

Then enter this formula anywhere, and copy down as many rows as you wish to
display the amount of random numbers.

=INDEX(ROW($A$1:$A$49),RANK($A$1:$A$49,$A$1:$A$49))

Hit <F9> to return a new set.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:%23tQ503YLFHA.3500@TK2MSFTNGP14.phx.gbl...
> Check out this page of Chip Pearson's.
>
> Scroll down to "D", and see all the goodies he's got there concerning
> duplicates.
>
> http://www.cpearson.com/excel/topic.htm
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Nu-bEE" <bertie@basset.com> wrote in message
> news:OQkNXuYLFHA.3016@TK2MSFTNGP15.phx.gbl...
> > I have created a Lotto Checker, how can I stop a number from being
> repeated
> > in the `winning number` input cell/s
> >
> > I am already using validation to restrict the input from 1 to 49.
> >
> > If you can assist please post back to this board.
> >
> > PLEASE ALWAYS POST BACK TO THE BOARD, OTHEWRS MAY FIND THE SOLUTION
> HELPFUL
> >
> >
>

Hmmm...

I didn't read the post to be that they wanted to generate
a list of non-duplicating numbers. I read it to mean that
when the winning lottery numbers are drawn and I enter
them into my sheet how do I keep from accidentaly entering
any duplicates. So,

Assume you enter the winning numbers in A1:A6.

Select that range.
Data Validation
Allow: Custom
Formula:

=AND(A1>=1,A1<=49,COUNTIF(A$1:A1,A1)<=1)

Biff

>-----Original Message-----
>It seems I didn't quite comprehend your question as well
as Gord did.
>
>Here's a way to display the random *order* of a set
amount of digits, which
>in your case would be 1 to 49.
>You wouldn't have to sort with this procedure either,
just hit the <F9> key
>to recalc the sheet and produce a new set of non-
repeating numbers.
>
>In A1 enter:
>=RAND()
>And copy down to A49
>
>Then enter this formula anywhere, and copy down as many
rows as you wish to
>display the amount of random numbers.
>
>=INDEX(ROW($A$1:$A$49),RANK($A$1:$A$49,$A$1:$A$49))
>
>Hit <F9> to return a new set.
>--
>HTH,
>
>RD
>
>----------------------------------------------------------
-----------------
>Please keep all correspondence within the NewsGroup, so
all may benefit !
>----------------------------------------------------------
-----------------
>"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
>news:%23tQ503YLFHA.3500@TK2MSFTNGP14.phx.gbl...
>> Check out this page of Chip Pearson's.
>>
>> Scroll down to "D", and see all the goodies he's got
there concerning
>> duplicates.
>>
>> http://www.cpearson.com/excel/topic.htm
>> --
>> HTH,
>>
>> RD
>>
>> --------------------------------------------------------
------------------
>-
>> Please keep all correspondence within the NewsGroup, so
all may benefit !
>> --------------------------------------------------------
------------------
>-
>> "Nu-bEE" <bertie@basset.com> wrote in message
>> news:OQkNXuYLFHA.3016@TK2MSFTNGP15.phx.gbl...
>> > I have created a Lotto Checker, how can I stop a
number from being
>> repeated
>> > in the `winning number` input cell/s
>> >
>> > I am already using validation to restrict the input
from 1 to 49.
>> >
>> > If you can assist please post back to this board.
>> >
>> > PLEASE ALWAYS POST BACK TO THE BOARD, OTHEWRS MAY
FIND THE SOLUTION
>> HELPFUL
>> >
>> >
>>
>
>.
>

You mean I *double* miscomprehended ???<bg>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Biff" <biffinpitt@comcast.net> wrote in message
news:255d01c52dc4$4a06d990$a601280a@phx.gbl...
Hmmm...

I didn't read the post to be that they wanted to generate
a list of non-duplicating numbers. I read it to mean that
when the winning lottery numbers are drawn and I enter
them into my sheet how do I keep from accidentaly entering
any duplicates. So,

Assume you enter the winning numbers in A1:A6.

Select that range.
Data Validation
Allow: Custom
Formula:

=AND(A1>=1,A1<=49,COUNTIF(A$1:A1,A1)<=1)

Biff

>-----Original Message-----
>It seems I didn't quite comprehend your question as well
as Gord did.
>
>Here's a way to display the random *order* of a set
amount of digits, which
>in your case would be 1 to 49.
>You wouldn't have to sort with this procedure either,
just hit the <F9> key
>to recalc the sheet and produce a new set of non-
repeating numbers.
>
>In A1 enter:
>=RAND()
>And copy down to A49
>
>Then enter this formula anywhere, and copy down as many
rows as you wish to
>display the amount of random numbers.
>
>=INDEX(ROW($A$1:$A$49),RANK($A$1:$A$49,$A$1:$A$49))
>
>Hit <F9> to return a new set.
>--
>HTH,
>
>RD
>
>----------------------------------------------------------
-----------------
>Please keep all correspondence within the NewsGroup, so
all may benefit !
>----------------------------------------------------------
-----------------
>"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
>news:%23tQ503YLFHA.3500@TK2MSFTNGP14.phx.gbl...
>> Check out this page of Chip Pearson's.
>>
>> Scroll down to "D", and see all the goodies he's got
there concerning
>> duplicates.
>>
>> http://www.cpearson.com/excel/topic.htm
>> --
>> HTH,
>>
>> RD
>>
>> --------------------------------------------------------
------------------
>-
>> Please keep all correspondence within the NewsGroup, so
all may benefit !
>> --------------------------------------------------------
------------------
>-
>> "Nu-bEE" <bertie@basset.com> wrote in message
>> news:OQkNXuYLFHA.3016@TK2MSFTNGP15.phx.gbl...
>> > I have created a Lotto Checker, how can I stop a
number from being
>> repeated
>> > in the `winning number` input cell/s
>> >
>> > I am already using validation to restrict the input
from 1 to 49.
>> >
>> > If you can assist please post back to this board.
>> >
>> > PLEASE ALWAYS POST BACK TO THE BOARD, OTHEWRS MAY
FIND THE SOLUTION
>> HELPFUL
>> >
>> >
>>
>
>.
>