Results 1 to 4 of 4

Thread: How to match name on Excel?

  1. #1
    Join Date
    Nov 2011
    Posts
    63

    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.

  2. #2
    Join Date
    Mar 2011
    Posts
    542

    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.

  3. #3
    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????

  4. #4
    Join Date
    May 2011
    Posts
    410

    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.

Similar Threads

  1. how to match data between 2 columns in excel
    By dankar in forum Windows Software
    Replies: 1
    Last Post: 25-04-2013, 11:16 AM
  2. How to match records from two Sheets in Microsoft Excel
    By Shaina Na in forum MS Office Support
    Replies: 3
    Last Post: 14-01-2012, 06:13 PM
  3. iTunes Match just does not get it right
    By Harbir in forum Portable Devices
    Replies: 6
    Last Post: 11-01-2012, 06:44 AM
  4. Excel-match formula with 3 criterias
    By daniel84 in forum Windows Software
    Replies: 1
    Last Post: 23-07-2011, 04:05 AM
  5. Match Destination Formatting as default in Excel 2007
    By NamJam in forum Windows Software
    Replies: 7
    Last Post: 29-11-2010, 10:59 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,643,345,305.58360 seconds with 17 queries