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

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.

Thanks for that James. Works perfectly.

Mal

"James Ravenswood" wrote in message
...
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.

One further point.
The formular from James works 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?
Thanks,
Mal

"Mal" wrote in message
nd.com...
Thanks for that James. Works perfectly.

Mal

"James Ravenswood" wrote in message
...
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.

Well I don't know how elegant the programing is but I have come up with the
following that seems to work.

Name in A1,Formulars in B1 & C1

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.

Mal


"Mal" wrote in message
d.com...
One further point.
The formular from James works 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?
Thanks,
Mal

"Mal" wrote in message
nd.com...
Thanks for that James. Works perfectly.

Mal

"James Ravenswood" wrote in message
...
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.

On Wed, 28 Dec 2011 07:38:36 +1000, "Mal" wrote:

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

Might there be duplicate last names in either of the lists?

What is your ultimate goal here?

Ron,
I am sorting the names in Last Name / First Name order outside the text I
have sent to this forum so this should not be a problem.
My problem was with the "Mc Mahon" (with space) or McMahon (no space) both
the same person which I think I have now solved with the help of this group.
I just thought my solution may have been of interest to others in the group.
I realise it is not bulletproof with split names such as "van der vort" etc
potential problems but I can live with that.

Mal



"Ron Rosenfeld" wrote in message
...
On Wed, 28 Dec 2011 07:38:36 +1000, "Mal" wrote:

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

Might there be duplicate last names in either of the lists?

What is your ultimate goal here?

On Wed, 4 Jan 2012 16:15:47 +1000, "Mal" wrote:

Ron,
I am sorting the names in Last Name / First Name order outside the text I
have sent to this forum so this should not be a problem.
My problem was with the "Mc Mahon" (with space) or McMahon (no space) both
the same person which I think I have now solved with the help of this group.
I just thought my solution may have been of interest to others in the group.
I realise it is not bulletproof with split names such as "van der vort" etc
potential problems but I can live with that.

Mal

OK, I was going to offer a VBA solution that would match things up as well as possible, but since you already have a working solution, I will put that off.