Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 . |
#6
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i stop automatic decimals after number ex 5280.? | Excel Discussion (Misc queries) | |||
Count the number of dates in a range that are earlier than today. | Excel Worksheet Functions | |||
How to add one number to a range of numbers | Excel Worksheet Functions | |||
I WANT TO SET PRINT RANGE TO NUMBER OF COLUMNS | Setting up and Configuration of Excel | |||
GET.CELL | Excel Worksheet Functions |