How to Compare Two Columns in Excel: VBA vs Python

Introduction

Comparing two columns in Excel is a common data-cleaning and validation task. Whether you need to find differences, matches, or unique values, Excel offers several ways to do it. Two popular approaches are VBA macros and Python scripts.

Get Andrew Wilson’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

VBA is built into Excel and works well for manual, one-off comparisons. Python, on the other hand, is ideal for automation, batch processing, and server-side workflows. In this guide, you will learn how to compare two columns in Excel using both VBA and Python, with complete code examples and a comparison section to help you choose the right method.

Note: The Python example uses Free Spire.XLS for Python, a free library that can read and write Excel files without requiring Microsoft Office.

📝 Requirement: Compare Two Columns in Excel

Read an Excel worksheet, compare data in Column A and Column B, and output two types of results:

1. Differences — Items where Column A and Column B are different. This includes values that exist in A but not B, and values that exist in B but not A.
2. Matches — Items where Column A and Column B are the same. These can be highlighted.

Write the results to Column C so differences are easy to review.

Convention: Data starts from row 2 (row 1 is the header). Only valid, non-empty cells are compared.

Method 1: Compare Two Columns in Excel with VBA

VBA is embedded in Excel and uses the Excel application object directly. It is a good choice if you are already working in Excel, prefer manual macro execution, and do not want to install extra software. The main drawback is that it depends on Office and is less convenient for cross-platform or remote batch execution.

VBA Code to Compare Two Columns

Sub CompareRowByRow()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim lastRowA As Long, lastRowB As Long, maxRow As Long
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    maxRow = Application.WorksheetFunction.Max(lastRowA, lastRowB)
    
    ' Clear results in column C
    ws.Columns("C").ClearContents
    ws.Columns("C").Interior.ColorIndex = xlNone
    ws.Cells(1, 3).Value = "Comparison Result"
    
    Dim i As Long
    Dim valA As String, valB As String
    
    For i = 2 To maxRow
        valA = Trim(CStr(ws.Cells(i, 1).Value))
        valB = Trim(CStr(ws.Cells(i, 2).Value))
        
        ' Skip rows where both columns are empty
        If valA = "" And valB = "" Then GoTo NextRow
        
        If valA = valB Then
            ' Match: A and B are the same (and non-empty)
            ws.Cells(i, 3).Value = "Match"
            ws.Cells(i, 3).Interior.Color = RGB(200, 255, 200) ' Light green highlight
        Else
            ' Difference: includes one side empty, or both non-empty but unequal
            ws.Cells(i, 3).Value = "Difference"
        End If
        
NextRow:
    Next i
    
    MsgBox "Row-by-row comparison complete. Results have been written to column C."
End Sub

How the VBA Code Works

• Reads values from Column A and Column B row by row.
• Uses Trim(CStr(...)) to ignore leading and trailing spaces and to unify numbers and text.
• Skips rows where both columns are empty.
• Writes “Match” and applies a light green background when values are equal and non-empty.
• Writes “Difference” for all other cases, including when one side is empty or both sides are non-empty but unequal.

Advanced VBA: Show Specific Differences

If you want Column C to display the actual difference values, replace the writing rules with this:

If valA = valB Then
    ' Match: A and B are the same (and non-empty)
    ws.Cells(i, 3).Value = "Match: " & valA
    ws.Cells(i, 3).Interior.Color = RGB(200, 255, 200) ' Light green highlight
Else
    ' Difference
    If valA <> "" And valB = "" Then
        ws.Cells(i, 3).Value = "Difference: A only - " & valA
    ElseIf valA = "" And valB <> "" Then
        ws.Cells(i, 3).Value = "Difference: B only - " & valB
    Else
        ws.Cells(i, 3).Value = "Difference: " & valA & " vs " & valB
    End If
End If

Method 2: Compare Two Columns in Excel with Python

Python can run independently of Office and is easy to schedule. This makes it a strong choice for batch files, cron jobs, and server-side automation. Free Spire.XLS for Python can load Excel files, traverse cells, and write results without starting Excel.

Install Free Spire.XLS for Python

pip install spire.xls.free

Python Code to Compare Excel Columns

from spire.xls import *
from spire.xls.common import *

# Load workbook
workbook = Workbook()
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\example.xlsx")
sheet = workbook.Worksheets[0]
max_row = sheet.LastRow
sheet.Range[1, 3].Value = "Comparison Result"
for row in range(2, max_row + 1):
    cell_a = sheet.Range[row, 1]
    cell_b = sheet.Range[row, 2]
    
    val_a = str(cell_a.Value).strip() if cell_a.Value is not None else ""
    val_b = str(cell_b.Value).strip() if cell_b.Value is not None else ""
    
    # Skip rows where both columns are empty
    if not val_a and not val_b:
        continue
    
    cell_c = sheet.Range[row, 3]
    
    if val_a == val_b:
        cell_c.Value = "Match"
        cell_c.Style.Color = Color.get_Yellow()
    else:
        cell_c.Value = "Difference"
# Save result
workbook.SaveToFile("comparison_result.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
print("Row-by-row comparison complete. Results have been written to column C.")

How the Python Code Works

• Reads Column A and Column B row by row.
• Uses str(...).strip() to convert values to strings and remove leading/trailing spaces.
• Skips rows where both columns are empty.
• Writes “Match” and applies a yellow highlight when values are equal.
• Writes “Difference” when values are not equal.

VBA vs Python: Which Should You Use?

When deciding between VBA and Python for comparing Excel columns, consider the following factors.

• Runtime environment: VBA depends on Office or WPS and runs inside Excel. Python with Free Spire.XLS runs in an independent Python environment and does not start Excel.
• Deployment: VBA is embedded in an .xlsm file. Python scripts plus dependencies can be packaged as an .exe.
• Batch processing: VBA can lag when processing many files, and stability depends on Excel. Python is good for batch traversal of folders, though you should check free version limits.
• Debugging: VBA offers breakpoints in the VBA editor, letting you see sheet changes immediately. Python relies on logs and output files.
• Best for: VBA is best for one-off, manual comparisons inside Excel. Python + Free Spire.XLS is best for automated pipelines, scheduled jobs, and server-side processing.

Conclusion

Comparing two columns in Excel is a classic data task, and both VBA and Python are valid solutions. VBA is a native Excel option that works well for manual, in-Excel comparisons. Python, especially with Free Spire.XLS for Python, gives you automation power without needing desktop Office.

Choose VBA when you need a quick, interactive macro. Choose Python when you need batch processing, scheduled comparison, or server-side automation. By matching the tool to your environment, you can compare Excel columns faster and more reliably. ✅