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

x
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.

Older 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
Next
If Not bMatched Then
.Cells(lngRowA, 4) = "No"
End If
Next
End With
End Sub
``````
Author 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.
Older 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?
Author Commented:
I've been adding more names all day. Here is the updated sheet.

Thanks,
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.
Older 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
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"
Else
varDataA(lngRow, 4) = "No"
End If
End With
Next

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

Experts Exchange Solution brought to you by

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

Author 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!!!
Older 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.