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




.