Compare Two Lists with VBA
The task of comparing two lists in Excel is a common problem which is often solved with formula. The following Excel VBA procedure will isolate the unique values in column A which are not in column B and will output the results to column D. For this VBA procedure to work the data must be side by side in Column A and Column B. This allows the VBA procedure to run down both sequential lists and compare each data set in turn and then output the result in the next column.
The list on the right is the unique items from List 1, when comparing List 1 to List 2. This procedure is similar to the article Find Duplicates with VBA. The solution uses the scripting dictionary to achieve a solution.
Compare Lists Tutorial
The following YouTube video goes through this procedure using the scripting dictionary.
Compare Lists with VBA
The scripting dictionary is a very efficient way to evaluate data in Excel. The way the scripting dictionary works is only data which is unique is allowed into the dictionary. Therefore comparing two lists is a task which is perfect for the dictionary. If the data matches each line in column A with all of the lines in column B. On the face of it this sounds inefficient as the dictionary has to compare one item with all the items in the list of column B. However, the scripting dictionary is not like normal Excel VBA in terms of speed, it moves at lazer like speed and will do things very quickly. So using the scripting dictionary for the task of comparing two lists is ideal.
Compare List 1 to List 2
The following is the VBA procedure to output the unique items in list 1 that are not in list 2.
Dim ar as Variant
Dim var()
Dim i As Long
Dim n As Long
ar=Range("a9").CurrentRegion 'Change Input to suit
ReDim var(1 To UBound(ar, 1), 1 To 1)
For i = 1 To UBound(ar, 1)
For i = 1 To UBound(ar, 1)
var(n, 1) = ar(i, 1)
End Sub
The way the above works is that LIST 2 is put into the dictionary first, this happens here;
.Item(ar(i, 2)) = Empty
Where the 2 above refers to the second column. It puts the unique data one by one into the dictionary for comparison purposes later on.
If Not .exists(ar(i, 1)
The above is a test to see if the item appears in the entire dictionary. The code checks the entire dictionary without a loop, so if it does not exist then the data is added to the second array (var).
var(n, 1) = ar(i,1)
In this way the results of the dictionary are not output, just the items not in the dictionary. In a way the dictionary is being used as an intermediary between the array (ar) and the array (var) to arbitrate what is not already in the dictionary. For comparison purposes the two lists no matter the length are evaluated in lightning speed and the differences are output super fast.
Compare List 2 to List 1
If we wanted to do the opposite then a few things need to change in the coding. The following is the code to highlight the unique items in List 2 which are not in list 1.
Dim ar as Variant
Dim var()
Dim i As Long
Dim n As Long
ar=Range("a9").CurrentRegion 'Change Input to suit
ReDim var(1 To UBound(ar, 1), 1 To 1)
For i = 1 To UBound(ar, 1)
For i = 1 To UBound(ar, 1)
var(n, 1) = ar(i, 2)
End Sub
The following 2 lines have changed to make the comparison switch between both columns.
If Not .exists(ar(i, 2)) Then
var(n, 1) = ar(i, 2)
Where the highlighted value was changed from a 1 to a 2.
The following Excel file shows a practical example of this VBA procedure.
