Formula for detecting inexact duplicates?

Hi there,

I have a relatively large Excel 2003 file (2 worksheets of ~5000 companies) that needs to be uploaded to a Microsoft Dynamics database. I want to upload it without any duplicates, and that includes any companies that are very similar to pre-existing companies in the MD database. For example, I might have a company called "Microsoft, Inc" in my upload file, and the existing file in the database is called "Microsoft Inc." which is very similar but is off by 2 characters, which would create a duplicate. Is there a formula that I can apply to my data that will flag entries that are off by say, 2-5 characters? Any ideas?

Thanks!

Thanks, that got me on the right track at least.

That sounds like a good idea, but I'm not sure how to do a match on a certain number of characters. Could you give an example please?

Like this:

=LEFT(A1,5)=LEFT(B1,5) will return TRUE if the first 5 characters match and FALSE if they don't

Thanks for your reply, but wouldn't that mean that the company names in columns A and B would have to line up? I don't necessarily have the same number of companies in both lists, so they wouldn't line up one to one.

OK, then like this:

=MATCH(LEFT(B2,C1)&"*",A:A,0)

this will produce a numeric result for a match of the first x number of characters of the value of B2 match the same first x number of characters of a value in column A - the value of x is in C1 - and it will produce #N/A where no such match is found

Thanks again. I'm having trouble running the formula though, even though it seems like I'm right on the verge of getting it. Let's say my array is column A, and value I'm trying to find is B2, B3 and so on. Where does the C1 in your formula come into play? And the &"*" ?