asked on

EXCEL Name Matching

I am using Excel 2010.

I have an Excel workbook with two sheets. Sheet1 contains amongst other columns, two named FIRST_NAME (column E) and LAST_NAME (column F). Sheet2 contains, amongst others, LEGAL_FIRST_NAME, LEGAL_LAST_NAME, PREFEERED_FIRST_NAME and PREFERRED_LAST_NAME (columns B, C, D and E respectively).

I want to flag rows in the first sheet where the first and last name don't match either the legal or preferred last name in sheet 2.
The legal and preferred names can be (and 95% of the time are) the same. However, I also want to consider it a match if I match either first name AND either last name.

For example, the name in Sheet1 might be LIZZIE WILLIAMS-HESS. On Sheet2, the preferred name might be LIZZIE WILLIAMS and the legal name ELIZABETH WILLIAMS-HESS. This would be considered a match because the first name matched one of the two and the last name matched one of the two, even though they weren't the same pair (preferred vs. legal).

I've tried using VLOOKUP and logic functions, but the issue is it does not 'sync' rows. If I find a match on first name in any row and a match on last name in any row (not necessarily the same one), the function returns a match. I can easily write a macro to iterate through each row but I really want to try to keep this using built-in functions and not have to write a macro,

Any suggestions?

Patrick Matthews

A sample file would be helpful. Use "fake" names if necessary to protect confidential information.

Alan Varga

I think this will do the trick.
29012942-name-matching.xlsx

Patrick Matthews

VieleFragen,

Not sure your approach will do it. If we look for a name like John Solo, your formulae will call it a match.

Patrick

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

ASKER

Just had something come up and will be out of town until next Tuesday. Please don't think I'm ignoring anyone.

Alan Varga

Patrick,
You're right, I forgot to account for found first and last names both being in the same row. I downloaded your example, because I'm going to be needing something similar at work over the next couple of weeks. Thanks for sharing!
Alan

Alan Varga

Patrick,

In looking at this further, everything works as long as the first+last on Sheet 1 matches either a first+last preferred combination or a first+last legal combination. However, there isn't a match on first preferred+last legal, or for first legal+last preferred.

I added a couple of more names to each sheet, with a Remarks column. I think I understand your formulas fairly well, but not well enough to modify them. Can you update them to account for these exceptions?
Q_29012942_v2.xlsx

D B

ASKER

I actually considered the possibility adding four columns on Sheet2. One with a concatenation of preferred first and last name, one with a concatenation of legal first and last name, one with the concatenation of preferred first and legal last and one with concatenation of legal first and preferred last name and just search the four columns with an OR function. Kind of kludgy but it should work if all else fails.

Patrick Matthews

dbbishop, did you review what I posted?

D B

ASKER

Patrick,
I won't be able to until at least Tuesday.

D B

ASKER

Patrick,

Per VieleFragen's comment, the code will not match across legal first and last or legal last and first. I apologize for not getting back sooner but since posting have gone on short-term disability leave at work and am not working on this. However, it was not a high priority item at the time and I do have the workbook at home and would like to continue playing with it. Any updates? Also please be patient as it may take a few days to review and get back to you.

D B

ASKER

I ended up creating a macro because of spec changes. As it turns out there is also a Rep ID. The Rep ID can be associated with either the rep or an assistant assigned to the rep. The new criteria was to match on the Rep ID and then attempt to match on the name (First & Last) as initially indicated. As this required matching on three columns in the row, a macro appeared to be the way to go.

If anyone else is interested, here is the code:

Const repIdColumn As Integer = 1
Const firstNameColumn As Integer = 4
Const lastNameColumn As Integer = 5
Const LegalFirstNameColumnOffset As Integer = 2
Const PreferredFirstNameColumnOffset As Integer = 4
Const LegalLastNameColumnOffset As Integer = 3
Const PreferredLastNameColumnOffset As Integer = 5
Const flagColumn As Integer = 11


Sub MatchReps()

Dim ClientAccessSheet As Worksheet
Dim AllRepsSheet As Worksheet
Dim foundCell As Range
Dim firstFind As String

Dim lastClientRow As Long
Dim ClientRepLastName As String
Dim ClientRepFirstName As String
Dim ClientRepID As String
Dim curRow As Long

Set ClientAccessSheet = ActiveWorkbook.Worksheets(1)
Set AllRepsSheet = ActiveWorkbook.Worksheets(2)

lastClientRow = ClientAccessSheet.Cells(Rows.Count, "A").End(xlUp).Row

For curRow = 2 To lastClientRow
    With ClientAccessSheet
        .Cells(curRow, flagColumn).Value = ""
        ClientRepID = Trim(.Cells(curRow, repIdColumn))
        ClientRepFirstName = Trim(.Cells(curRow, firstNameColumn))
        ClientRepLastName = Trim(.Cells(curRow, lastNameColumn))
    End With
    With AllRepsSheet.Range("A:A")
        Set foundCell = .Find(ClientRepID, lookat:=xlPart)
        If Not foundCell Is Nothing Then
            firstFind = foundCell.Address
            Do
                If (Trim(foundCell.Offset(0, LegalFirstNameColumnOffset).Value) = ClientRepFirstName _
                            Or Trim(foundCell.Offset(0, PreferredFirstNameColumnOffset).Value) = ClientRepFirstName) _
                        And (Trim(foundCell.Offset(0, LegalLastNameColumnOffset).Value) = ClientRepLastName _
                            Or Trim(foundCell.Offset(0, PreferredLastNameColumnOffset).Value) = ClientRepLastName) Then
                    ClientAccessSheet.Cells(curRow, flagColumn).Value = "Found"
                    Exit Do
                    Set foundCell = .FindNext
                End If
            Loop While Not foundCell Is Nothing And foundCell.Address <> firstFind
        End If
    End With
Next
  
End Sub

Open in new window

I am going to leave this question open for a couple of days to see if Patrick can answer the remainder of the request (e.g. match on preferred first name/legal last name or vice versa). I will then accept and grade accordingly.

D B

ASKER

Grading it a B since it did not completely meet the requirements.