find / replace single digits

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Hey Violator behind the grassy knoll, welcome to the forum,

See the attached where I built you the game. Instead of 1-51, I put in the state abbreviations. See if this works for you. If not, tell me what should change. More picks? More Names? Validation dropdown of possible state names?

Pick some states game.xlsm

This is a 'low tech' proposal.
Place the date and lotto numbers in row 1.
Populate each range of pool numbers using formulas similar to: =IF(ISERROR(MATCH(B2,$N$1:$S$1,0)),B2,"")
As an option you could group the ranges of pool numbers as modeled in the attached copy of the file to make it easier to identify whose row wins.
Let us know if you have any questions.

Not to worry. Lets see if this is helpful for the Jan 31 and Feb 2 sheets. If so we'll then attempt to replicate the Feb 2 copy sorted sheet.
Note that on the Jan 31 and Feb 2 sheets I placed the formula based proposal in columns M:W so that it could be compared to what is already there.
First the order of the names will need to remain the same as on the All entries sheet so column M on both sheets is populated using: ='all entries'!A1
On the Jan 31 sheet N1:U188 are populated using: =IF(ISERROR(MATCH('all entries'!B1,$N$190:$S$190,0)),'all entries'!B1,"")
W1:W188 are populated using: =COUNT(N1:U1)
On the Feb 2 sheet N1:U188 are populated using: =IF(ISERROR(MATCH('Jan 31'!N1,$N$190:$S$190,0)),'Jan 31'!N1,"")
Note that the lottery numbers for each sheet are on row 190.
Let us know if you have any questions.

Please adhere to forum rule #8.
I understand that you are attempting to do all of your deletions through the find and replace feature and that the single digit numbers are the ones that are causing the problems. In my opinion there is no way to format the numbers such that what you want to do can be accomplished. Formatting only affects the way a number is displayed not the actual value in the cell. If you select a cell that is displaying 03 and look in the formula bar, you will see that 3 is what is actually in that cell. When you attempt to find and replace 3 the feature will also find and replace the 3 in 30:39, 13, 23, and 43.
I further understand that when you get ready to show the updated results you want to rank by count of unmatched numbers remaining and to have all unmatched numbers pushed to the left side of the table. As stated in post#7 the "copy sort sheet" is something we can attempt to replicate after establishing that the formula is correctly deleting the matches.
In the file attached to post #7 look at 1 in a million's entries and notice that the first number is a 1. Since 1 is one of the numbers drawn in the Jan 31 lottery it was deleted from 1 in a million's number list on the Jan 31 sheet. further note that it is still deleted when looking at the Feb 2 sheet.
If you have spotted something that the formula is doing wrong, please cite cell and sheet reference so that I may attempt to correct the issue.
I will be away for a while so I will likely not respond until the early part of next week.
Let us know if you have any questions.

To show that formulas can produce the same end result as the method you are now using take a look at the Feb 2 copy sorted sheet, columns M:W
Column M is populated using: =INDEX('Feb 2'!M$1:M$188,AGGREGATE(15,6,ROW('Feb 2'!A$1:A$188)/('Feb 2'!W$1:W$188=W1),COUNTIFS(W$1:W1,W1)))
Columns N:U are populated using: =IFERROR(INDEX($X1:$AE1,AGGREGATE(15,6,(COLUMN($X1:$AE1)-COLUMN($W1))/($X1:$AE1<>""),COLUMNS('Feb 2'!$A1:A1))),"")
Column W is populated using: =SMALL('Feb 2'!W$1:W$188,ROWS(W$1:W1))
Columns X:AE are populated using: =INDEX('Feb 2'!N$1:N$188,MATCH($M1,'Feb 2'!$M$1:$M$188,0))
Note that columns X:AE may be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.