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

Have you ever faced a situation where you need to compare a “Member List” and an “Event Attendee List” to find “Members who did not attend the event”? This task involves comparing two lists to find the “difference” data that exists only in one of them.

With VBA, you can automate such complex matching processes. This article explains two methods: a basic method combining loops with the COUNTIF function, and a high-speed method using the “Dictionary Object,” which is frequently used by advanced VBA users.

Method 1: Using For Each Loop and COUNTIF Function

This method has logic that is intuitive and easy to understand. It performs two checks using loops: “Does each item in List A exist in List B?” and “Does each item in List B exist in List A?”

Completed VBA Code

Sub ExtractDifferences_WithLoop()

    ' Declare variables
    Dim listA As Range, listB As Range
    Dim cell As Range
    Dim resultsSheet As Worksheet
    Dim outputRow As Long

    '--- Settings ---
    Set listA = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
    Set listB = ThisWorkbook.Worksheets("Sheet2").Range("A1").CurrentRegion
    
    ' Prepare sheet for outputting results
    Set resultsSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(2))
    resultsSheet.Name = "Diff_Data"
    resultsSheet.Range("A1").Value = "Data only in List A"
    outputRow = 2
    '--- End Settings ---
    
    Application.ScreenUpdating = False

    '--- 1. Search for data that exists only in List A ---
    For Each cell In listA.Columns(1).Cells
        ' If the same value does not exist in List B (COUNTIF result is 0)
        If WorksheetFunction.CountIf(listB.Columns(1), cell.Value) = 0 Then
            cell.EntireRow.Copy resultsSheet.Cells(outputRow, 1)
            outputRow = outputRow + 1
        End If
    Next cell
    
    '--- 2. Search for data that exists only in List B ---
    resultsSheet.Cells(outputRow, 1).Value = "Data only in List B"
    outputRow = outputRow + 1
    
    For Each cell In listB.Columns(1).Cells
        ' If the same value does not exist in List A
        If WorksheetFunction.CountIf(listA.Columns(1), cell.Value) = 0 Then
            cell.EntireRow.Copy resultsSheet.Cells(outputRow, 1)
            outputRow = outputRow + 1
        End If
    Next cell

    resultsSheet.UsedRange.Columns.AutoFit
    Application.ScreenUpdating = True
    
    MsgBox "Extraction of difference data is complete. (Loop Method)"

End Sub

While this method is easy to understand, it checks every combination of the two lists, so performance may drop significantly if the data exceeds several thousand rows.

Method 2: Using Dictionary Object (Recommended)

The Dictionary object is a feature for handling high-speed associative arrays (key-value pairs) in VBA. Using this allows you to extract differences extremely quickly, even with large amounts of data.

[Important] Preparation: Reference Setting

For this method, you need to open Tools > References in the VBA editor and check “Microsoft Scripting Runtime”.

Completed VBA Code

' Reference: Microsoft Scripting Runtime
Sub ExtractDifferences_WithDict()

    Dim listA As Range, listB As Range
    Dim resultsSheet As Worksheet
    Dim dict As New Scripting.Dictionary ' Dictionary Object
    Dim cell As Range
    Dim key As Variant
    Dim outputRow As Long
    
    '--- Settings ---
    Set listA = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
    Set listB = ThisWorkbook.Worksheets("Sheet2").Range("A1").CurrentRegion
    Set resultsSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(2))
    resultsSheet.Name = "Diff_Data_Fast"
    '--- End Settings ---
    
    Application.ScreenUpdating = False
    
    ' 1. Register all items from List A into Dictionary
    For Each cell In listA.Columns(1).Cells
        If Not dict.Exists(cell.Value) Then
            dict.Add cell.Value, cell.Row
        End If
    Next cell
    
    ' 2. Check items in List B. If common, remove from Dictionary
    For Each cell In listB.Columns(1).Cells
        If dict.Exists(cell.Value) Then
            ' Common items are removed from Dictionary
            dict.Remove cell.Value
        Else
            ' Items only in List B are copied to result sheet immediately
            If outputRow = 0 Then
                resultsSheet.Range("A1").Value = "Data only in List B"
                outputRow = 2
            End If
            cell.EntireRow.Copy resultsSheet.Cells(outputRow, 1)
            outputRow = outputRow + 1
        End If
    Next cell
    
    ' 3. Copy remaining items in Dictionary (Data only in List A)
    If dict.Count > 0 Then
        resultsSheet.Cells(outputRow, 1).Value = "Data only in List A"
        outputRow = outputRow + 1
        For Each key In dict.Keys
            listA.Rows(dict(key)).EntireRow.Copy resultsSheet.Cells(outputRow, 1)
            outputRow = outputRow + 1
        Next key
    End If
    
    resultsSheet.UsedRange.Columns.AutoFit
    Application.ScreenUpdating = True

    MsgBox "Extraction of difference data is complete. (Dictionary)"
End Sub

Since this code utilizes the high-speed search capability of Dictionary, processing completes almost instantly even with tens of thousands of rows.

Summary

When extracting differences between two lists, if the data volume is large or performance is required, we strongly recommend using the Dictionary object method.

It might feel a bit difficult at first, but mastering this technique will greatly expand the range of data processing you can handle with VBA.