Matching - Fuzzy Match or Other techniques?

Question

I have a list of equipment IDs. Some are from an SAP datasource and others were manually entered. When manually entered correctly, our platform can match them to an SAP entry. Sometimes, individuals will enter them incorrectly and the logic isn't able to match them.

Here's an example:

FE12345

12345 --> in this case 12345 is the incorrectly added entry. It needs to be matched with FE12345

I'd also be interested to see other similar IDs that may be off by a single digit, but the priority is the above situation.

I tried Fuzzy Match but it doesn't seem to be able to identify the above strings as a match. What other ways could I do this?

Thanks!

cpet13 · Accepted Answer

@Alefiyah dealing with manually entered data sounds tricky. From what I understand of the Fuzzy Match tool, it is really good at things like when a street address is 12345 St. and matching that with 12345 Street. But in the example you provided, FE12345 and 12345, that is a little trickier. For a situation like that, you could potentially use a formula(?) tool and say that if the first value, FE12345, contains the second, 12345, then the second value should be replaced with the first. Then join them together. This, however, would be complicated if you had two IDs that were close and would meet that criteria but should not, in fact, be joined. Eg. FE12345 and FE123.

MelGibson · Accepted Answer

Another thought is that if it doesn't match should it not fall out and be audited. Like @cpet13 said it can be tricky if you have id's that are close and if you do a contain formula you run the risk of a false match.  Unless - your ids have the same start ie. FE then in that case you can use a formula to identify them by either comparing the right numeric value with the numeric value of the field.