On Dec 27, 4:38*pm, "Mal" wrote:
I have 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?

Ant help appreciated.

Thanks,

Mal

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. In
C1 enter:
=SUBSTITUTE(A1,B1,"") and copy down

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

Now you can sort and try to match them up.