matching same/similar records

[msnook]

I have a challenging problem of finding entries that are similar and may not be exact matches. Any assistance will be greatly appreciated.

I am working on a list that has two columns of information like the ones below.

Item Number Serial Number
1 193955601 3091669457
2 99073070804 70804-01
3 99073070804 99073-0708-04

My challenge is to compare individual rows in the two columns and find the occurances where the 'serial number' was entered incorrectly using the 'Item Number'. The problem is that there is a large number of variables to take into consideration. In some instances they are exact matches which are easy to find, but in other examples they are only similar. There may be numbers dropped, changed positions, dashes added, one number differences, etc. My answer to this issue is to break the 'item number' down digit by digit and see if each digit is present in the 'serial number'. If a certain percentage of them are matches then assume that the numbers match. Is there an easy way to do this or another option that I am not thinking of?

Thanks in advance for any assistance.


Mike

 

[SkipVought]

Hi,

WOW!

Whose mind do you have to read?

How you you devine that row 1 is related, or is that one just downright wrong?

Row 3 may be as simple as removing non-numeric characters.

Dies row 2 qualify as a "match"

You have some RULES to work out!!!!!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[msnook]

The errors are when the numbers match so #1 (which doesn't match)is a correct entry and the other two were entered in error (and therefore match). Thanks for the input. I thought of many partial remedies but nothing that would catch the majority of the varialbles (Other than what I outlined in the original submission).

 

[mscallisto]

How many rows do you have to examine and how often are they created?

If you don't have too many (a few hundred maybe?) you might save time doing them manually.

Sometimes there are just too many variables to try to program and expect any degree of success.

good luck

 

[stevexff]

Do you have any way of validating the item or the serial numbers? Like looking them up from a catalogue (database) or a range of values? This might allow you to determine which rows are bogus?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]