Does name match email address?

[dawnd3]

HI All, I Have a list of email addresses and names in Excel 2007. It was a very messy file so there are many where the correct name did not get matched up with the email address. Is there a way to go to each record and compare the name and email and have it tell me if a certain amount of characters are the same. i.e. if the name is John Smith and the email is jsmith@yahoo.com I would want it to recognize that they both have SMITH. Can't use LEFT, MID or anything like that since each one is different. I just want it to go down the column and have it give me a yes if there are X number of matching characters and a no if there is not. Is there such a function?

Thanks,

Dawn

 

[SkipVought]

Hi,

What a mess!

The short answer is, No. There is no ONE 'silver bullet.' You must perform a lot of painstaking categorization and analysis.

You mentioned a name, John Smith. Are your names ALL ONLY a first name and last and NEVER anything else? Are your first and last names in separate fields?

Use the Data > Text to columns feature to separate the email name from the domain. Then post several representative names & emaile names, withour the domains so we can give you some pointers regarding your data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dawnd3]

Too much work, I was looking for a quick and easy function to just tell me yes or no if the email and name likely belonged together. The client said I can just get rid of the names but I was hoping to salvage them. It was a mess to begin with. Oh well. Thanks Skip!

Dawn

 

[strongm]

>if the email and name likely belonged together

Soundex, Metaphone and/or Double Metaphone may be of use here.

 

[Gruuuu]

Soundex, Metaphone and/or Double Metaphone

I had considered suggesting these, but really this situation is an absolute mess. You will end up with a lot of false matches if the names are similar (or the same, John Smith?)

Furthermore, unless the email user naming conventions are enforced by company policy, you'll have a bunch of goofy usernames.

If this is like, a vendor list or something, I suppose it may be moderately successful, for a nontrivial amount of work.

 

[strongm]

>I just want it to go down the column and have it give me a yes if there are X number of matching characters


I asumed that this requrement suggested that there would then be a manual review to see if the match is good or not, thus reducing the data set for manual checking. Under this scenario the Soundex algorithm and similar may (and just may) be useful. They certainly are not automated solutions.