There has been a number of articles written on thesmallman.com 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:
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
Next
ar = Sheet1.Cells(1).CurrentRegion.Value
n = 1
For
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 :)
