Stop Number duplication in a range

[Nu-bEE]

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

 

[Gord Dibben]

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

 

[Ragdyer]

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

 

[Ragdyer]

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.

 

[Biff]

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

 

[RagDyeR]

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

Regards,

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

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