Compare Two Lists on Different Sheets and Output Matches in Excel

Compare Two Lists on Different Sheets and Output Matches in Excel

Compare Lists VBA Scripting

There has been a number of articles written on about matching information.  I tend to lean towards the scripting dictionary.  I had an email from a friend recently saying they were trying to adapt code from these two posts:

Compare Two Worksheets

Compare Two Lists with VBA

The idea was to compare two lists on different sheets and output the matches to a third sheet.  So I decided to combine the two techniques into one.  It was not a great deal of work.  The general premise is that the scripting dictionary removes the non matching items and all that is left are the items that are matching.  The current region is trapped however only the two lists are compared.  The upside is that when a match is made the entire row for each match is returned to sheet3.

Sub CompareSolve()
Dim i As Long
Dim j As Long
Dim n As Long
Dim ar As Variant

ar = Sheet2.Cells(1, 3).CurrentRegion.Value

With CreateObject(“Scripting.Dictionary”)
.CompareMode = 1

For i = 2 To Ubound (ar, 1)
.Item(ar(i, 1)) = Empty

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

n = 1

i = 2 To Ubound (ar, 1)
If .exists(ar(i, 1)) Then
n = n + 1
For j = 1 To UBound(ar, 2)

ar(n, j) = ar(i, j)
Next j
End If
Next i
End With

Sheet3.Cells(1).Resize(n, UBound(ar, 2)).Value = ar
End Sub

The following Excel file shows the procedure in a practical context. The Excel workbook will copy all of the like data between Col C of sheet2 and Col A of sheet1 and move the data to the Output sheet. Simples 🙂


Please wait...

Subscribe to ExcelMOOC posts

Want to be notified when our Excel article is published? Enter your email address and name below to know all new Excel features and cool tips