Partial matches in lists of 6-digit numbers
Closed
baxterkix
Posts
2
Registration date
Friday June 14, 2019
Status
Member
Last seen
June 14, 2019
-
Jun 14, 2019 at 12:39 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 20, 2019 at 11:29 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 20, 2019 at 11:29 AM
Hello! I have a list of ~500 6-digit numbers. They were all hand entered, and we want to proof the list and see if any of them were duplicated by typing 1 digit in wrong. For example, If the number 123456 was entered, then when the number was encountered again it was mis-typed as 123457. Or 133456. Basically I want to see if any of these numbers are off by any 1 single digit. Any idea how I can do this?
This is what I've been trying on my test table:
=VLOOKUP("*"&LEFT(F3,5)&"*",$E$2:$F$24,2,0)
And it's just giving me "#N/A" even though there are definitely values it should catch.
This is what I've been trying on my test table:
=VLOOKUP("*"&LEFT(F3,5)&"*",$E$2:$F$24,2,0)
And it's just giving me "#N/A" even though there are definitely values it should catch.
Related:
- Partial matches in lists of 6-digit numbers
- Samsung mobile reset code 8 digit - Guide
- Please wait a few minutes to receive your 6-digit code before trying again. - Facebook Forum
- Samsung gt-e1200y mobile reset code 8 digit - Phones, PDA & GPS Forum
- 5 digit unlock code for nitro racing - Phones, PDA & GPS Forum
- 6 digit code problem fix ✓ - Messaging Forum
2 responses
Ok we just dealt with an issue such as this recently. Make certain the columns that you are using are actually marked up as number, and the text you want to return is marked up as text. Make certain the cells you are performing the vlookup function in, are marked up as general. Once we applied these formats to our sheet, the formula started returning the proper lookups! Make certain the cell contents are the proper types.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jun 20, 2019 at 11:29 AM
Jun 20, 2019 at 11:29 AM
Hi Baxter,
Even though I'm confused as to what you are trying to achieve, to make your formula produce a result try this:
=VLOOKUP("*"&LEFT(F3,5)&"*",$E$2:$F$24&"",2,0)
INSTEAD of pressing enter to confirm, press Ctrl+Shift+Enter to confirm formula. When done correctly the formula will be enclosed by curly brackets.
Best regards,
Trowa
Even though I'm confused as to what you are trying to achieve, to make your formula produce a result try this:
=VLOOKUP("*"&LEFT(F3,5)&"*",$E$2:$F$24&"",2,0)
INSTEAD of pressing enter to confirm, press Ctrl+Shift+Enter to confirm formula. When done correctly the formula will be enclosed by curly brackets.
Best regards,
Trowa
Jun 14, 2019 at 12:36 PM