Compare Two Excel Workbooks & Show Discrepancies

[davehunter]

Hi Guys!

I'm looking for some help with my excel workbooks.

I would like to be able to compare two workbooks, and display the discrepancies between them.

The two workbooks contain two columns - Surname & Date of Birth.

I want the macro to remove all people that match in the two workbooks, and leave the ones that don't.

I have provided two example workbooks with example details.

If anyone can help me it would be much appreciated.

Thanks!!
Dave

[davehunter]

Hi Guys,

I'm still looking for help on this if anyone has any answers

Thanks
Dave

[spreethi81]

Hi,

You may try this piece of code. May be it'll help you...

Sub RetrieveFileName()
' Set false when debug complete
Const bDebug As Boolean = True

Dim oWB1 As Workbook, oWS1 As Worksheet
Dim oWB2 As Workbook, oWS2 As Worksheet
Dim sFileName As String
Dim vWSName As Variant
Dim vWSNames As String
Dim i As Integer
Dim iRow As Long
Dim maxR As Long, maxC As Long

Set oWB1 = ActiveWorkbook
Set oWS1 = ActiveSheet

If MsgBox("Select Market Area Pricing File to compare against.", _
vbOKCancel, "Market Area Pricing") = vbCancel Then Exit Sub '---------->

sFileName = Application. GetOpenFilename
If sFileName = "False" Then Exit Sub '------------------------------------->
Workbooks.Open sFileName
Set oWB2 = ActiveWorkbook

If bDebug Then MsgBox ActiveWorkbook. Name

' make a list of visible sheets
For i = 1 To oWB2.Sheets.Count
If Sheets(i).Visible Then ' list visible sheets
vWSNames = vWSNames & i & " - " & Sheets(i).Name & vbCr
End If
Next i
vWSName = InputBox("Select Current Market Price Sheet" & vbCr & vWSNames)
If vWSName = "" Then Exit Sub '-------------------------------------------->
Sheets(Val(vWSName)).Select
If bDebug Then MsgBox ActiveSheet.Name
Set oWS2 = ActiveSheet

With oWS1.UsedRange
maxR = WorksheetFunction.Min(.Rows.Count, oWS2.UsedRange.Rows.Count)
maxC = WorksheetFunction.Min(.Columns.Count, oWS2.UsedRange.Columns.Count)
End With

' Activate the First Wookbook (the one you ran the Macro from)
oWB1.Activate ' (not necessary)

With oWS1
For iRow = 1 To maxR
Application.StatusBar = "Comparing cells " & Format(iRow / maxR, "0 %") & "..."
If .Cells(iRow, 1) = oWS2.Cells(iRow, 1) Then
With .Cells(iRow, 1).Offset(0, maxC - 1).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Next iRow
End With
End Sub

Regards,
Preethi