How do I match data from 2 worksheets and highlight the differences using collections in VBA?
Let’s say I have some data in sheet1 in column A which is my reference data. I also have data in sheet2 in column A which I get from my colleague in another city. Every evening we have to reply back whether the data matches or some of the data is different. I work for a courier company and the data consists of Airway Bills. I know how to solve this problem with Vlookup. I want to automate the process and also highlight the differences in sheet2. Can this be done through VBA in Excel?
You can easily solve this problem using two methods in Excel VBA:
(i) You can use a nested loop but with large amounts of data the process can become slow. This process compares each value in sheet1 with every value in sheet2. It then goes to the next value in sheet 1 and again compares the value with each value in sheet2 till the last value in sheet1 has been checked.
(ii) You can use the ‘collections’ feature in Excel VBA. Collections are a kind of arrays but with some key differences like: (a) A ‘collection’ does not have to have data of the same type. (ii) You can easily add items to a collection and memory allocation happens dynamically.
A ‘collection’ object uses 4 methods: Add, Count, Item and Remove.
The Add method, for example, is implemented as follows:
Collection.Add item, [key],[before],[after]
You can use the add method to create a list of unique data. In our example we’ll take the item value and assign it to the key value. Now duplicate items cannot be added to the list. If you should add data with a duplicate key, there will be an error. You can now use ‘on error resume next’ to bypass the error. In this manner you can skip the duplicates.
Now we’ll loop through our range in sheet1 and create a collection of unique values. Next we’ll loop through the data in sheet2 and add data to the collection only if its different and skip the duplicates using ‘on error resume next’. Also we’ll add the different data to the beginning of the collection. If no error occurs then the value was not in the collection and therefore also not in our sheet1 data. At this point of time we can also format the different data in sheet2 to highlight it. Before going out of the loop we can then ‘remove’ the added data from the collection. This ensures that our collection contains only the unique items from sheet1.
We do some initializations at the end so that our process works automatically.
‘we define our ranges and collection
Dim R1 As Range
Dim R2 As Range
Dim R As Range
Dim Nc As New Collection
‘we select our range R1 in sheet1
Set R1 = Intersect(.Range(“A:A”), .UsedRange)
‘we select our range R2 in sheet2
Set R2 = Intersect(.Range(“A:A”), .UsedRange)
‘Don’t display an error message if encounter a duplicate
On Error Resume Next
‘Create a collection from range R1 in sheet1
For Each R In R1
Nc.Add R.Value, R.Value
‘Now we loop through the data in sheet2 and check whether the same data is in sheet1
For Each R In R2
‘Reset the errors to 0
Err = 0
‘If the value is not in the collection add it to the beginning of the collection
Nc.Add R.Value, R.Value, 1
‘If no error occurred then the value wasn’t found in the collection and therefore also not on sheet 1
If Err = 0 Then
‘Fill the cell with red color to highligh it
R.Interior.Color = RGB(248, 78, 54)
‘set the font color to white
R.Font.ColorIndex = 2
‘Remove the first item in the collection. This is the last item that was added
‘This ensures that our collection contains only the unique items from sheet1
Err = 0
‘Resume standard error handling
On Error GoTo 0
The training video below shows the details of matching the data in the two Excel worksheets: