Comparing names in Excel sheets

In the attached XL2010 sheet, I want to know which people exists in either one sheet or both sheets. I need to do this by comparing the 3 fields FName, LName, and DOB across each record to see where there is a match and to return "Yes" or "No" in the corresponding field to the question "does this person exist in the other sheet"
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Someone may come up with a VLOOKUP or other formula but here's a macro that works.

Sub NameExists()
Dim lngLastRowA As Long
Dim lngLastRowI As Long
Dim lngRowA As Long
Dim lngRowI As Long
Dim bMatched As Boolean

With Sheets("ALLIANCE")
    lngLastRowA = .Range("A1048576").End(xlUp).Row
    lngLastRowI = Sheets("INTELIF").Range("A1048576").End(xlUp).Row

    For lngRowA = 2 To lngLastRowA
        bMatched = False
        For lngRowI = 2 To lngLastRowI
            If .Cells(lngRowA, 2) = Sheets("INTELIF").Cells(lngRowI, 2) And _
               .Cells(lngRowA, 1) = Sheets("INTELIF").Cells(lngRowI, 1) Then
                .Cells(lngRowA, 4) = "Yes"
                bMatched = True
                Exit For
            End If
        If Not bMatched Then
            .Cells(lngRowA, 4) = "No"
        End If
End With
End Sub

Open in new window

contrainAuthor Commented:
Hi Martin,

Thanks for responding. It seems to work for the first page, as far as I can see down the sheet, but then it just locks up and Excel doesn't respond and I have to close the program. I don't know what's causing that but it won't run the macro all the way down the column to all of the records before not responding.
Martin LissOlder than dirtCommented:
What do you mean by "the first page"? Is it that you really have a lot more records that in the workbook you posted? If so can you post the real workbook?
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

contrainAuthor Commented:
I've been adding more names all day. Here is the updated sheet.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
There is problem with your data on ALLIANCE sheet. How do you get data on this sheet?

Few issues with the data in col. C
1) These are not dates but the text as the first character in all the cells in col. C is ' i.e. an apostrophe.

2) Some of your text dates are invalid dates, invalid because excel cannot handle a date before 1900 and some of your dates are before 1900 like in C527 and C528 as those belongs to year 1884.

3) The year is not the only issue, some of your dates have invalid days and months.

So before comparing both the sheets on the basis of Last Name, First Name and DOB, you will need to fix the dates on ALLIANCE Sheet.
Martin LissOlder than dirtCommented:
The following compares the 3 columns as is and it uses a dictionary object so it's very fast. Run the FindNamesDriver sub.
Sub FindNamesDriver()
    FindNames "ALLIANCE", "INTELIF"
    FindNames "INTELIF", "ALLIANCE"
End Sub
Sub FindNames(strSheetA As String, strSheetB As String)

Dim objDic As Object
Dim lngLastRowA As Long
Dim lngLastRowB As Long
Dim varDataB As Variant
Dim varDataA As Variant
Dim strDicKey As String
Dim lngRow As Long

Set objDic = CreateObject("Scripting.Dictionary")
objDic.comparemode = vbTextCompare

lngLastRowA = Sheets(strSheetA).UsedRange.Rows.Count
lngLastRowB = Sheets(strSheetB).UsedRange.Rows.Count

varDataA = Sheets(strSheetA).Range("A2:D" & lngLastRowA).Value
varDataB = Sheets(strSheetB).Range("A2:C" & lngLastRowB).Value

For lngRow = LBound(varDataB) To UBound(varDataB)
    With objDic
        strDicKey = varDataB(lngRow, 1) & "^" & varDataB(lngRow, 2) & "^" & varDataB(lngRow, 3)
        If Not .exists(strDicKey) Then
            .Add strDicKey, strDicKey
        End If
    End With
Next lngRow

For lngRow = LBound(varDataA) To UBound(varDataA)
    With objDic
        If .exists(varDataA(lngRow, 1) & "^" & varDataA(lngRow, 2) & "^" & varDataA(lngRow, 3)) Then
            varDataA(lngRow, 4) = "Yes"
            varDataA(lngRow, 4) = "No"
        End If
    End With

Sheets(strSheetA).Range("A2:D" & lngLastRowA).Value = varDataA
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
contrainAuthor Commented:
This is the perfect solution to my problem! The Expert gave me the code I needed to solve a very complex problem in a completely satisfying way and made it easy to apply. Great Job!!!
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.