Compare Two Worksheets with VBA

Compare two worksheets with VBA and output the differences to a new worksheet.  With the following vba macro, you can compare the contents of two worksheets and only display the differences. The following will compare Column A in the active sheet (Sheet1) for example with Column A in Sheet2.  It uses the scripting dictionary so the process is very fast for high volumes of data.  Of course this procedure could be replicated with a Vlookup in native Excel without the output to a new area.

Option Explicit

Sub NoMatches() 'Excel vba to remove duplicates.
Dim dic As Object
Dim ar As Variant
Dim ar1 As Variant
Dim var As Variant
Dim i As Long
Dim n As Long

Set dic=Createobject("Scripting.Dictionary")
dic.CompareMode=1
ar=Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
var=Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Value
ReDim ar1(1 To UBound(var), 1 To 1)

'Loop through ar and add to Dictionary.
For i=1 To UBound(ar)
If Not dic.exists(ar(i, 1)) Then
dic.Add ar(i, 1), ar(i, 1)
End If
Next i

'Identify non Matches
For i=1 To UBound(var)
If Not dic.exists(var(i, 1)) Then
n=n + 1
ar1(n, 1)=var(i, 1)
End If
Next i

'Output Results Remove any Duplication
Sheet3.Range("E2:E" & UBound(var)).Value=ar1
Range("E2:E" & UBound(var)).RemoveDuplicates 1
End Sub

In the following file I have included 2 procedures. One compares two lists with vba and outputs items which are the same. This article is similar to the article on comparing two lists with VBA. The other compares two lists with vba and outputs the differences.

 

The following will compare the contents of each cell in each line in one sheet (multiple columns) with the contents of each line in another worksheet. This takes the concept from above one step further where you are comparing the contents of 2, 3, 10 etc items in a row.  The result is displayed in a new workbook listing all cell differences.

If you only want to compare one column (similar to the compare procedure above) of data you can adapt the following by putting the data in Column A of both sheets.  Ensure you leave one column blank (col B).  Or change the following lines;

ar=Sheet1.Cells(10, 1).CurrentRegion.Value
ar=Sheet2.Cells(10, 1).CurrentRegion.Resize(, UBound(v)).Value


So that the ranges cover just one column rather than the current region.

Option Explicit

Sub CompareIt() 'Excel VBA with scripting dictionary to compare sheets
Dim ar As Variant
Dim arr As Variant
Dim Var As Variant
Dim v()
Dim i As Long
Dim n As Long
Dim j As Long
Dim str As String

ar = Sheet1.Cells(10, 1).CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
.CompareMode = 1
ReDim v(1 To UBound(ar, 2))
For i = 2 To UBound(ar, 1)
For n = 1 To UBound(ar, 2)
str = str & Chr(2) & ar(i, n)
v(n) = ar(i, n)
Next
.Item(str) = v: str = ""
Next

ar = Sheet2.Cells(10, 1).CurrentRegion.Resize(, UBound(v)).Value

For i = 2 To UBound(ar, 1)
For n = 1 To UBound(ar, 2)
str = str & Chr(2) & ar(i, n)
v(n) = ar(i, n)
Next

If .exists(str) Then
.Item(str) = Empty
Else
.Item(str) = v
End If
str = ""
Next

For Each arr In .keys
If IsEmpty(.Item(arr)) Then
.Remove arr
Next
Var = .items: j = .Count
End With
With Sheet3.Range("a1").Resize(, UBound(ar, 2))
.CurrentRegion.ClearContents
.Value = ar
If j > 0 Then
.Offset(1).Resize(j).Value = Application.Transpose(Application.Transpose(Var))
End If
End With
End Sub

The Excel file attached shows workings with the cells in yellow showing the differences.