Data validation with wildcards

[Poco90]

I hope someone can help with a problem that is driving me soft? I want to enter a 15 digit part number into a cell and compare it to up to 5 different 15 digit part numbers (on the same sheet) and return a yes or no in an adjacent cell if there is a match or not to these 5. My problem is that ocaisionally an extra random digit or letter is put on the end of the part number. Could anyone suggest a formula for me please? I have being trying IF THEN statements but have had no luck. Thanks in advance,
Poco

 

[Damon Ostrander]

Hi Poco,

The formula is a bit long and tedius, but you could use:

=IF(OR(MID(A1,1,15)="183946301745528",MID(A1,1,15)="394687430255289",MID(A1,1,15)="987654321098765",MID(A1,1,15)="123456789012345"),"Yes","No")

Damon

 

[Damon Ostrander]

Hi again Poco,

I should also mention that with a few lines of VBA code it would be possible to "validate" the referenced cell (or range of cells) so that it would not be possible to enter a part number in the cell that is inconsistent with your criteria. If you would like the code for this, let me know.

Damon

 

[Oaktree]

Why not use = A1&"5" instead of manually typing the 15 digits?

 

[KenWright]

With myrange being your 5 valid values and myval being the reference to your lookup value

=COUNTIF(myrange,myval&"*")

Addendum

Sorry, forgot the Yes/No bit
=IF(COUNTIF(myrange,myval&"*")>0,"Yes","No")

 

[Poco90]

Just want to thank you all for your responses. I was going nowhere fast with my efforts. Thanks again,
Poco

 

[Poco90]

I think I need to be spoon fed or shot.

Damon
when I use your code for only numbers it works fine, but it doesn't seem to like my part numbers. I should have made it clearer to you that the suppliers product code is alpha numeric and may have spaces in the middle

for example
MNRIE14E0ABEJ220
MNRIE14E0AB J220
MCRIE03EZHEJ3R0
MCRIE03EZPEJ3R0
MCRIE03EZH J3R0

The list above is the range that I would call suitable for cell A1. The other problem I have is even though these are the genuine supplier part numbers, when we get the supplier part the label says MNRIE14E0ABEJ220 but it actually reads as MNRIE14E0ABJ220 J (I am inputting these parts with a barcode scanner)

I modified your code to =IF(OR(MID(A1,1,15)="MNRIETR14E0ABJ220",MID(A1,1,15)="MNRIE14E0AB J220",MID(A1,1,15)="MCRIE03EZPEJ3R0",MID(A1,1,15)="MCRIE03EZHEJ3R0"),"Yes","No") but I keep getting no.

Do you have any suggestions? If the VBA code is the way to go and the offer is still there, I'd would like to take you up on it if its not too much trouble? I know even less about VB than I know about excel.



Ken
When I try your code I get also get a no. I uses A5:A9 as my range and A1 as myval. Is there any way I (should say you) could modify your code for my part no.?

Thank you all again for your time and effort. Best regards,
Poco

 

[Damon Ostrander]

Hi Poco90,

The problem has nothing to do with numbers vs. characters, but rather in the fact that you appear to be using 16-digit part numbers rather than the 15 you mentioned in your problem statement. Either that, or the zero at the end of each of your strings is the "extra random digit" you mentioned. If the zero at the end is truly part of the part number you want to match and therefore some of the part numbers are 16 digits long, you must use 16 in the corresponding MID function, like this:

=IF(OR(MID(A1,1,16)="MNRIE14E0ABEJ220",MID(A1,1,16)="MNRIE14E0AB J220",MID(A1,1,15)="MCRIE03EZHEJ3R0",MID(A1,1,15)="MCRIE03EZPEJ3R0",MID(A1,1,15)="MCRIE03EZH J3R0"),"Yes","No")

where the last three are compared to 15 characters because they are 15 characters long.

If, on the other hand, the zero of the 16-character part numbers IS NOT part of the part number you want to compare (is an extra random digit), then do the compare against the part numbers without the zeros:

=IF(OR(MID(A1,1,15)="MNRIE14E0ABEJ22",MID(A1,1,15)="MNRIE14E0AB J22",MID(A1,1,15)="MCRIE03EZHEJ3R0",MID(A1,1,15)="MCRIE03EZPEJ3R0",MID(A1,1,15)="MCRIE03EZH J3R0"),"Yes","No")

Note that the space does count as a character, as you presumed.

You also mentioned that MNRIE14E0ABEJ220 scans as MNRIE14E0ABJ220 J, which not only has an extra two characters at the end, but also is missing the E. If the " J" is what you refer to as an extra random character, the MID function in the formula should get rid of it, but the missing E is a problem. To match this you should have

MID(A1,1,15)="MNRIE14E0ABJ220"

in the formula, i.e., comparing it to the part number without the E.

Does this help?

Damon

 

[Poco90]

Damon,
I will have to admit defeat. The parts I am scanning are throwing up too many variants, multiple spaces in the part number as well as the random character on the end of the number so I think this is my problem. Thank you again for all your input. Rgds,
Poco

 

[Damon Ostrander]

Hi again Poco90,

What is not possible with worksheet formulas is often easily accomplished with a VBA user-defined function (UDF). If the problem is being able to tolerate extra spaces inserted anywhere in the string as well as a random character on the end, this can be accomplished via a UDF. Would you like to give this approach a try?

Damon