Nokia C3: How to match name on Excel?

How to match name on Excel?
I am having a list of names in Excel 2003, Last Name in column A first name in B sorted in Alfa order.
I get a list from an external source that includes names that I wish to match to my list.The problem is that the format of the supplied list is completely different to my list and I cannot get the source to change their format.
e.g.

My List
A B
Black Bill
Brown J
Jones Amy
Smith John
White Sandra

The supplied list is in Excel in one column as follows and not sorted:

Amy Jones
B. Black
S J White
John J Smith
Joe Brown

The only constants are the Last Name and the punctuation and spaces vary. I was thinking of maybe somehow sorting backwards on the last name? Any help appreciated.
Reply With Quote
#2
Old 04-01-2012
Erakna Erakna is offline
Member

Join Date: Mar 2011
Posts: 507
Re: How to match name on Excel?
Assume your supplied list is in column A. The first step is to get the last names in a separate column.

In B1 enter:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))))+1,255) and copy down

The second step is to get the other material in a separate column.

InC1 enter:
=SUBSTITUTE(A1,B1,"") and copy down

So if A1 contained: John J. Smith
B1 would display: Smith
C1 would display: John J.

Now you can sort and try to match them up.
Reply With Quote
#3
Old 04-01-2012
Super-Man Super-Man is offline
Member

Join Date: Nov 2011
Posts: 63
Re: How to match name on Excel?
The solution provided in the previous post working perfectly with the exception of names such as
Mc Mahon. This must be because of the space between the Mc"space"Mahon.Is there any way to account for these Mc "space" names????
Reply With Quote
#4
Old 04-01-2012
Essien Essien is offline
Member

Join Date: May 2011
Posts: 414
Re: How to match name on Excel?
Well I don't know how elegant the programming is but I have come up with the following that seems to work.

Name in A1,Formulas in B1 & C1
Code:

b1=IF(ISNUMBER(FIND("Mc ",A1,1)),MID(A1,(FIND("Mc
",A1,1)),255),MID(A1,FIND(CHAR(1),SUBSTITUTE(A 1,"
",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255))

c1=SUBSTITUTE(A1,B1,"")

May be of use to someone else.