[Excel VBA] How to Compare Two Tables and Extract Common Data Rows

In data analysis, comparing two lists—such as “Last Month’s Customer List” and “This Month’s Customer List”—to extract only the customers present in both is a frequent task. This is the process of finding the “intersection” of two datasets.

VBA allows for the automation of this matching and extraction process. This article explains two methods: a basic method combining loop processing with the COUNTIF function, and a high-speed method utilizing Excel’s “Advanced Filter.”

Method 1: Using For Each Loop and COUNTIF Function

This method sequentially checks each item in the first list to see if it exists in the second list using the COUNTIF function. The logic is intuitive.

VBA Code

Sub ExtractMatches_WithLoop()

    ' Declare variables
    Dim listA_Range As Range, listB_Range As Range
    Dim cell As Range
    Dim resultsSheet As Worksheet
    Dim outputRow As Long

    '--- Settings ---
    ' List 1 (Source) in Column A of Sheet1
    Set listA_Range = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
    ' List 2 (Target for comparison) in Column A of Sheet2
    Set listB_Range = ThisWorkbook.Worksheets("Sheet2").Range("A1").CurrentRegion.Columns(1)
    
    ' Prepare sheet for output
    Set resultsSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(2))
    resultsSheet.Name = "ExtractionResult"
    outputRow = 1
    '--- End Settings ---
    
    Application.ScreenUpdating = False

    '--- Loop through each cell in List 1 ---
    For Each cell In listA_Range.Columns(1).Cells
        ' Check if the value exists in List 2 using COUNTIF
        If WorksheetFunction.CountIf(listB_Range, cell.Value) > 0 Then
            ' If found, copy the entire row to the result sheet
            cell.EntireRow.Copy resultsSheet.Cells(outputRow, 1)
            outputRow = outputRow + 1
        End If
    Next cell

    Application.ScreenUpdating = True
    
    MsgBox "Common data extraction complete. (Loop Method)"

End Sub

While easy to understand, this method has the disadvantage of slow processing speeds when the source list contains thousands or tens of thousands of rows.

Method 2: Using Advanced Filter (Recommended)

This method calls Excel’s built-in “Advanced Filter” function from VBA. The code is concise, and processing is overwhelmingly faster.

Preparation

This method requires a list with headers as the “Criteria Range.” It assumes that the first row of List 2 (Sheet2), which serves as the comparison target, has headers identical to List 1.

VBA Code

Sub ExtractMatches_WithAdvancedFilter()

    ' Declare variables
    Dim listA_Range As Range
    Dim listB_CriteriaRange As Range
    Dim resultsSheet As Worksheet

    '--- Settings ---
    ' Source data (including headers)
    Set listA_Range = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
    ' Criteria data (including headers)
    Set listB_CriteriaRange = ThisWorkbook.Worksheets("Sheet2").Range("A1").CurrentRegion
    
    ' Prepare sheet for output
    Set resultsSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(2))
    resultsSheet.Name = "ExtractionResult_Fast"
    '--- End Settings ---

    '--- Execute Advanced Filter ---
    listA_Range.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=listB_CriteriaRange, _
        CopyToRange:=resultsSheet.Range("A1"), _
        Unique:=False

    MsgBox "Common data extraction complete. (Advanced Filter)"

End Sub

Key Points of the Code

listA_Range.AdvancedFilter …

This command executes the Advanced Filter.

• Action:=xlFilterCopy: Specifies the mode to copy filter results to another location.
• CriteriaRange:=listB_CriteriaRange: Specifies the cell range serving as the search criteria. Here, List 2 functions as the criteria, including its headers.
• CopyToRange:=resultsSheet.Range("A1"): Specifies the destination cell for the extraction results.
• Unique:=False: A setting to include duplicate records (set to True if duplicates should be ignored).

This single command triggers Excel’s internal optimized matching process, instantly copying only the rows from List A that match the conditions in List B to the result sheet.

Summary

When comparing two lists to extract common data, use AdvancedFilter unless there is a specific reason not to. Utilizing Excel’s built-in specialized functions provides far better performance and simpler code than constructing custom logic with VBA loops.