Checking for duplicates w/different iterations

[BoilerBob]

I searched the board and found useful tools for checking for duplicates, but have a challenge when data is coming from two diffent sources. I would like "AM BEST", "A.M. Best" and "A. M. Best" to show up as duplicates, but could not find an elegant way.

My current solution is to replace (CTRL-H) all periods, commas, and spaces with null.

Any more insightful solutions?

 

[agihcam]

why dont you post sample of your data so that everybody could have an idea as to where would you want to evaluate your duplicates records?

 

[BoilerBob]

Sample would be a database with current clients such as

Abbot Labs
Abbey Road
ABC Corp
A. M. Best
Aramark

....

And a prospect list with

Abbot Laboratory
Anheuser Busch
A.B.C. Corp
A.M. Best
.....

I would like to see if the prospects are in our current list. In the sample above, Anheuser Busch would be the only 'new' client to add to our list. A typical lookup would have all the companies not being duplicates because of slight variations.