+ Reply to Thread
Results 1 to 7 of 7

Need formula to match names

  1. #1
    Registered User
    Join Date
    02-15-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    21

    Need formula to match names

    I am comparing data from two different software systems that contain employee names. Each system has a different convention on how names are entered and listed on the reports. First report (A) lists names "first,mi,last,suffix", second report (B) lists names "last, suffix, first, mi".

    For example; Jane E. Doe-Martin M.D. is an employee. She recently got married so she added Martin to her original name.

    System A lists here name as "jane, h, doe, md",
    System B lists here name as "doemartin, md, janee".

    Each name is provided by the employee, and entered by someone else often at different times so it's common for the names to be different, slightly mispelled, not updated to reflect name changes, etc. This makes reconciling transactions between the two systems very challenging. Multiply this by 30,000 employee records I have to reconcile regularly and it's obvious that someone is working lots of extra hours, namely me.

    I've had limited success parsing out the names and using the LEFT function in a variety of ways to match names between the two systems. However, I notice that Excel 2010 has this search feature that searches on any fragment of the word. Excel Search.png

    Does excel have a similar function that will enable me to comare records?

    Thanks in advance!

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Need formula to match names

    Silly Question. Do you not use staff numbers or national insurance numbers on the systems?

  3. #3
    Registered User
    Join Date
    02-15-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need formula to match names

    The two systems are third party and the current reports we have access to do not list common employee profile data such as SSN, which is in the database, but I have to go the vendor and pay for the information through the purchasing process which typically is not fast.

  4. #4
    Registered User
    Join Date
    02-15-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need formula to match names

    I should clarify that the systems are from two different vendors, not the same one.

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Need formula to match names

    Ok I think I can help you with this.

    We will need to create a macro to check each entry in list a against list b.

    I have tested the formula to that I would use on the attached extract.

    if you can send me dummies of list A and b so that I can see the formats

    Then I will create something for you.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-15-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need formula to match names

    Thank you but company policy doesn't allow transfer of files across the internet. Any chance you could paste the VBA code? I'll try it out and let you know if it works. Thanks!!

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Need formula to match names

    Did you check the sample formula?

    Are the two lists on one sheet?

    Is each entry in one cell or in adjacent cells?

    There is a lot of information missing from your original post.

    Without this information we don't have a starting point.

    perhaps you can provide more details.

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1