Filter for suffix [x] when a comparing file has _x suffix

[tomtom91]

I have two huge excel files, lets call them "to be fixed" and "check". The "to be fixed" contains some suffix that end on "_1" etc. or "[1]" etc. The "check" file, that I want to compare the first file to, only contains "_1" suffixes but SOME mean basically the same. So e.g. ERR_PI[1] is ERR_PI[1] in the "to be fixed" file but ERR_PI_1 in the "check" File and ERR_PVS_H_1 is the same in the "to be fixed" and "check" file.

The final goal is to sort out every entry in "fixing" that isn't in "check". Any good ideas? I used VLOOKUP(x,y,1,TRUE) and FALSE but FALSE doesn't detect the difference in "[1]" VS "_1" etc. and TRUE returns entries that just begin with "ERR_" and calls it close enough.

[Bob Phillips]

Something like this?

=OR(ISNUMBER(MATCH(B2,K:K,0)),ISNUMBER(MATCH(SUBSTITUTE(B2,"[1]","_1"),K:K,0)))

You will need to adjust the cells and columns, and add the workbook names.

[tomtom91]

Something like this?

=OR(ISNUMBER(MATCH(B2,K:K,0)),ISNUMBER(MATCH(SUBSTITUTE(B2,"[1]","_1"),K:K,0)))

You will need to adjust the cells and columns, and add the workbook names.

Yes that works. But unfortunately just for "1". I have indexes going from 0 to x. Is there a way to search for indexes going from 0 to let's say 10 to be safe? Alternatively I can use 10 columns and do a comparison for indexes 0 to 10. Not very effective and I would prefer a all-in-one-solution. Or I guess a Micro and for loop is needed.

[georgiboy]

Elaborating on Bob's formula, maybe the below would work.

=OR(ISNUMBER(MATCH(B2,K:K,0)),ISNUMBER(MATCH(SUBSTITUTE(SUBSTITUTE(B2,"[","_"),"]",""),K:K,0)))

[Bob Phillips]

Building some more on this, should you have a set of values that are similar but not the same as another set, but not quite as well-formed as in georgiboy's suggestion, then you could try something like



=OR(ISNUMBER(MATCH(B2,K:K,0)),ISNUMBER(MATCH(SUBSTITUTE(B2,{"[1]","[2]","{Z}"},{"_1","_2","!Z"}),K:K,0)))