Using VBA Collections To Compare Two Sets Of Data In Excel

2 января, 2019

Articles

, , , ,

A common task for Excel users is to compare two separate lists of information with similar data. Usually the goal is to find duplicates, update data or transfer data from one list to another. For example:

  • Merge two separate customer lists which might contain common entries
  • Update product details by transferring data from one list to another
  • Identify duplicates across the two lists

We’ll write some simple VBA code to identify some data in one worksheet based on the information in another sheet. The scenario we’ll look at is updating a master price list based on a separate update sheet.

Our code will assume the update file has a listing of unique product codes in the first column and the new prices in column(2). We’ll create a new collection of product codes to enable us to directly access the data in the master file which has an identical structure.

Finding Data Efficiently In A Spreadsheet With VBA

The problem with finding data in Excel is sometimes you either have to loop through every cell or use a filter or match function each time the data needs to be found. In our price update scenario, the looping structure would be something like this:


For each cell in updateFile
For each cell in masterFile
'find the matching entry
next

next

For a small data set this loop creates few problems but what if there were over 1000 entries? The code might take a while to run; instead we’ll look at some code to take us directly to the matching entry in the master file:


For each cell in updateFile
' go to the matching cell in the masterFile

next

Using A Collection To Index A Worksheet

The first task is to create an index of the master sheet product codes and their row numbers in a new collection


Dim productCodes As New Collection

sheets("master").activate

Range("a1").CurrentRegion.Columns(1).Select
For Each c In Selection
productCodes.Add c.Row, c.Text

Next

We’ve created an index of product codes by adding the row number of each code and, importantly using the product code as the unique key. This means we can directly access the product code and get the row number in the master file.

Updating The Master File By Using The Product Code Collection

Now, all the code needs to do is loop through the update file and directly access the matching product code in the master file and update the price.


Sheets("update").Activate

Range("a1").CurrentRegion.Columns(1).Select
For Each c In Selection

' get the latest price from the update file and access the 

' row number of the matching product code in the master file
newPrice = c.Offset(0, 1).Value

rowNo = productCodes(c.Text)
' update the master file

Sheets("master").Activate
Range("b" & rowNo).Value = newPrice
' go to the next product to update

Sheets("update").Activate

Next

Summary

Instead of repeatedly looping through data in Excel to find a particular cell, it makes sense to directly access a cell if we know the value. This is a good example of using VBA to address a common task and making the process more efficient and productive.

Похожие записи