Comparing lists of names in Excel, accounting for duplicate last names

Question

I have two lists of names in Excel '07. Two columns in each file: first name and last name. I'd like to be able to tell which names in each list (name = first, last) appear in the other list. None of the methods I can think of account for more than one column at a time -- e.g., I can see how many "Smith"s there are, or how many "Albert"s, but I can't tell how many "Albert Smith"s there are.

Thoughts?

Edit: Obviously I can concatenate, but I'd like this approach to be generalizable to more than two columns of data.

Answer 1

The easiest way to is create a third column for both lists using CONCATENATE and then do a vlookup using this new column.

Answer 2

Unfortunately, this is a pretty common task in Excel for which the standard answer is as Joshua Smith says - build a combined key by concatenating the available columns. If you are concerned about collisions (e.g. the straight concatenation of multiple columns might leave different values with the same output), such as the following, then use a delimiter (e.g. the pipe character |).

Col A    Col B   Col C  Combined Key
 aaa      bbb     ccc    aaabbbccc
 aa       aa      aaa    aaaaaaa    -- Bad match...
 aaa      a       aaa    aaaaaaa    -- Bad match...

You can, of course, write a custom macro function to do this for you. The logic would be something like VLOOKUP:

Public Function VMatch(ByVal lookFor As Range, ByVal lookIn As Range) As String
    'Make sure column count matches (at least!)
    If lookFor.Columns.Count  lookIn.Columns.Count Then
        'Oops...
        VMatch = "ERROR: Column counts do not match"
        Exit Function
    End If
    'Start looking through the target range for
    'a match with the source range
    Dim blnFound As Boolean
    Dim blnRowOK As Boolean
    blnFound = False
    Dim iCol As Integer
    Dim iRow As Long
    Dim numCols As Integer
    numCols = lookFor.Columns.Count
    'Loop through all rows
    For iRow = 1 To lookIn.Rows.Count
        'Assume current row might be ok...
        blnRowOK = True
        'Loop through columns
        For iCol = 1 To numCols
            'Test for mis-match only
            If lookFor.Cells(1, iCol).Value  lookIn.Cells(iRow, iCol).Value Then
                blnRowOK = False
                Exit For
            End If
        Next
        'If row is still ok, we've found a match!
        If blnRowOK Then
            blnFound = True
            Exit For
        End If
    Next
    'If blnFound is true, we found a match
    If blnFound Then
        VMatch = "Match"
    Else
        VMatch = "No Match"
    End If
End Function

Note: The function above works and is not susceptible to "false positives" - it also tries to be less inefficient by jumping out if it hits a match, but I couldn't guarantee it will work in all cases.

To use the function, you would reference the range of all columns on the given row as the lookFor and the entire range of all possible matching rows in the lookIn, e.g. =VMatch(A1:C1,Sheet2!A1:C29) if the thing you were matching was on the current sheet cells A1:C1 and the other data set were on Sheet2 going from the first row down to row 29.

Answer 3

Update: Figured it out! Sumproduct does all that work for me. Here's a formula:

=SUMPRODUCT(($G$8:$G$110=C28)*($F$8:$F$110=D28))

This assumes that the reference first names are stored in G, last names in F, and that the names I'm looking for are in C (First) and D (Last) respectively. Output is 1 for a match, 0 for no match. Only produces a match when adjacent cells match.