Unsorted Data – Matching email addresses with Company Name and Name – impossible task?

[jmoore83]

I have a database of circa 50k details and the email addresses have somehow become mismatched to the contact details.
<o> </o>
I was wondering if there was a way of matching the majority of the emails back to the contact details.
<o> </o>
There are multiple columns (around 15), but a few more important than others.
Column A = Company Name
Column B = email address
Column C = First Name
Column D = Last Name
Column E = Full Name
<o> </o>
Obviously, In most cases part of A will be in B. i.e. A = EXCEL Ltd, and B includes @EXCEL.co.uk.
<o> </o>
Further to this, the emails (B) will usually include some of C, D or E.
<o> </o>
i.e. if name is John Smith, then email will be Smithj@, or johns@ or jsmith@.
<o> </o>
Due to my lack of Excel experience, I was wondering if there was a way of sorting this data, or if I was just stuck?
<o> </o>
Thanks in advance on any tips!

 

[BrianB]

*Try this for a starter (works by finding "." and "@") omits ".com" etc.

Email address in A1
First Name =LEFT(A1,FIND(".",A1,1)-1)
Last Name =MID(A1,LEN(B1)+2,FIND("@",A1,LEN(B1)+1)-LEN(B1)-2) (nb. uses cell with First Name)
Company Name =MID(A1,FIND("@",A1,1)+1,LEN(A1)-FIND("@",A1,1)-4)

You could then sort your main list and this list by Last Name to see what matches you get. Then Company name.

*If you have an "OK" column you can periodically sort on this column to cut/paste matched names to another sheet. Just an "x" or whatever will separate the blanks in this column from found ones.

*If too many names are similar you can add columns to concatenate them for matching.

Eg.
FredSmith
JohnSmith
BillSmith

*With lists/columns sorted side by side you can add a column to check matches automatically. eg.

=IF(UPPER(C1)=UPPER(M1),"X","")

Best of luck.

 

[BrianB]

Try this for a starter (works by finding "." and "@")

*Email address in A1
First Name =LEFT(A1,FIND(".",A1,1)-1)
Last Name =MID(A1,LEN(B1)+2,FIND("@",A1,LEN(B1)+1)-LEN(B1)-2) (nb. uses cell with First Name)
Company Name =MID(A1,FIND("@",A1,1)+1,LEN(A1)-FIND("@",A1,1)-4)

You could then sort your main list and this list by Last Name to see what matches you get. Then Company name.

*If you have an "OK" column you can periodically sort on this column to cut/paste matched names to another sheet. Just an "x" or whatever will separate the blanks from found ones.

*If too many names are similar you can add columns to concatenate them for matching.

Eg.
FredSmith
JohnSmith
BillSmith

*With lists/columns sorted side by side you can add a column to check matches automatically. eg.

=IF(C1=M1,"X","")