VBA Excel: How to Find Unique Values in Two Columns

To find distinct values across two Excel columns with VBA, scan both columns into a dictionary and write each value only the first time it appears. The Windows macro below reads A2:A… and B2:B…, ignores blank and error cells, and writes the combined unique list in column D.

What “unique values in two columns” can mean

There are two common interpretations, and they produce different results. This macro makes one list from the contents of both columns: if A contains Apple and B also contains Apple, the output contains Apple once. It is useful for combining customer names, product codes, categories, or other values collected in separate lists.

“Unique” can also mean rows that are distinct as pairs. For example, the pair North / Blue differs from North / Red, even though North appears more than once. That task should keep or remove duplicate rows, rather than merge every cell into one list. A third meaning is values that occur exactly once in the source; that requires counting occurrences, not merely removing duplicates. The code here performs the first task: a distinct union of both columns.

VBA macro to combine the columns

This version assumes row 1 contains headings, source data is in columns A and B, and column D is available for the result. It uses a late-bound Scripting.Dictionary, so no reference needs to be added in the VBA editor. Text comparisons ignore differences in letter case, while text values and numeric values are kept as different types.

Option Explicit

Sub UniqueValuesFromTwoColumns()
    Dim ws As Worksheet
    Dim dict As Object
    Dim lastA As Long, lastB As Long, lastOut As Long
    Dim r As Long, i As Long
    Dim items As Variant

    Set ws = ActiveSheet
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    ' Find the last used row in each source column.
    lastA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Scan each column from row 2, below its header.
    For r = 2 To lastA
        AddUniqueValue dict, ws.Cells(r, "A").Value2
    Next r
    For r = 2 To lastB
        AddUniqueValue dict, ws.Cells(r, "B").Value2
    Next r

    ' Clear the previous result in the reserved output column.
    lastOut = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    If lastOut >= 2 Then ws.Range("D2:D" & lastOut).ClearContents
    ws.Range("D1").Value = "Unique values"

    ' Write the dictionary items below the output heading.
    items = dict.Items
    For i = 0 To dict.Count - 1
        ws.Cells(i + 2, "D").Value = items(i)
    Next i
End Sub

Private Sub AddUniqueValue(ByVal dict As Object, ByVal v As Variant)
    Dim key As String

    ' Ignore cell errors, truly empty cells, and empty-text results.
    If IsError(v) Or IsEmpty(v) Then Exit Sub
    If VarType(v) = vbString Then
        If Len(v) = 0 Then Exit Sub
        key = "S|" & CStr(v)
    ElseIf VarType(v) = vbBoolean Then
        key = "B|" & CStr(v)
    ElseIf IsNumeric(v) Then
        key = "N|" & CStr(CDbl(v))
    Else
        key = TypeName(v) & "|" & CStr(v)
    End If

    If Not dict.Exists(key) Then dict.Add key, v
End Sub

The key prefixes prevent a text entry such as 001 from being treated as the same item as numeric 1. The macro preserves the first version of a repeated value it encounters. Because it scans column A from top to bottom and then column B, the result follows that encounter order; it does not sort the output alphabetically.

How to run the macro in Excel for Windows

Save a copy of the workbook first. A macro changes cells, and its changes cannot be reversed with Ctrl+Z.
Confirm that the source data is in columns A and B, with headings in row 1. Reserve column D for the results; the macro clears old contents below D1 before writing the new list.
Open the Visual Basic Editor with Alt+F11. On a keyboard without function keys, use the relevant Fn key with F11. Choose Insert > Module in the editor, then paste in the complete code.
Return to Excel, select the worksheet containing the data, and run UniqueValuesFromTwoColumns from Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

For a workbook that must retain its macro, save it as an Excel Macro-Enabled Workbook (.xlsm). Excel for the web can open an .xlsm file but cannot create, edit, or run its VBA macros. On Excel for Mac, the Visual Basic Editor and VBA are available, but this particular CreateObject(“Scripting.Dictionary”) approach may fail because the Windows scripting component is not consistently available. Use the formula alternative below on Mac, or replace the dictionary with a Mac-compatible implementation before running VBA there.

What the macro includes and leaves out

Blank cells and formulas that return an empty string are skipped. Cells containing Excel errors, such as #N/A, are also skipped rather than copied to the output. A cell containing one or more spaces is not blank to this code, so a space-only entry will appear as a value. If you want to trim spaces or treat them as blank, clean the source data or adjust the helper procedure deliberately.

The dictionary uses text comparison, so Apple and apple count as the same text value. This is convenient for names and labels, but it may be wrong for codes where capitalization matters. The code also treats numeric 1 and text “1” as different values, and preserves the first encountered spelling or capitalization. Dates read through Value2 are numeric serial values; the output may need a date number format if you want it displayed as a date rather than as a serial number.

Errors are deliberately omitted because converting an error value into a dictionary key needs separate handling. If those errors are meaningful in your data, decide whether to retain them as one shared output item or report them separately instead of silently treating them as ordinary text.

Distinct values versus distinct two-column rows

Do not select both columns and assume Excel’s Remove Duplicates command will make a combined one-column list. When both columns are selected, Excel evaluates duplicate records using the selected columns together. Thus rows North / Blue and North / Red are different records; the command does not pull North, Blue, and Red into a single column and deduplicate those individual cells.

For a distinct list of cell values, the macro above is the relevant approach. For distinct pairs, select the data range and use Data > Remove Duplicates, then select the columns that define a duplicate row in the dialog. If you want a non-destructive view of distinct records, Data > Advanced provides an option to copy unique records to another location. These row-oriented tools solve a related but different problem.

Formula alternative for Microsoft 365

If VBA is not essential, a dynamic-array formula can stack the two ranges and return one copy of each value. With source values in A2:A100 and B2:B100, enter this formula in an empty cell:

=UNIQUE(VSTACK(A2:A100,B2:B100))

VSTACK combines the ranges vertically, and UNIQUE returns the distinct entries as a spilling result. This formula is available in Microsoft 365 and Excel 2024; UNIQUE alone is also available in Excel 2021, but VSTACK is not. The formula may include a blank if either source range contains blank cells. To exclude blanks, use this version with the same assumed ranges:

=UNIQUE(FILTER(VSTACK(A2:A100,B2:B100),VSTACK(A2:A100,B2:B100)<>""))

Dynamic-array formulas are often simpler for a live result that updates with source cells. VBA is more useful when a user needs a repeatable button or process that writes values to a fixed output area. Neither option sorts automatically in the examples shown; sorting can be added as a separate step if required.

Common problems and safe handling

Wrong worksheet: The macro uses ActiveSheet. Click the intended sheet before running it, or change the code to refer to a named worksheet explicitly.
Headers treated as data: The loops start at row 2. If there are no headers, change both starting row numbers from 2 to 1.
Existing data overwritten: The macro clears D2 down to the last used cell in column D. Keep that output area clear of unrelated information, or change the output column and cleanup range together.
Nothing appears: Confirm that the input is in A and B, that values begin on row 2, and that macros are enabled for this trusted workbook. Check whether the active sheet is the expected sheet.
Unexpected duplicates or missing distinctions: Review capitalization, leading or trailing spaces, and whether a value is stored as text or as a number. The code intentionally ignores letter case but does not trim spaces.
Dictionary creation fails: This code is designed for Excel on Windows. On a Mac, use the formula approach or a VBA method that does not depend on Scripting.Dictionary.

Only enable macros in workbooks from sources you trust. Before running any macro that clears cells or writes results, save a separate copy and inspect the code’s input and output ranges. VBA changes cannot be undone with Ctrl+Z, so a backup is the practical way to restore the original workbook if the macro is run on the wrong sheet or range.

Frequently Asked Questions

Can VBA find unique values across two Excel columns?
Yes. The macro scans both columns and stores only the first occurrence of each value, producing one combined list in the output column.

Does this macro treat uppercase and lowercase as different?
No. It sets the dictionary to text comparison, so Apple and apple are considered duplicates. If capitalization must distinguish values, the comparison method needs to be changed.

How do I find values that occur exactly once?
That is a frequency-count task, not a distinct-list task. Count each value across both source columns and keep only those whose total count is one; this macro instead keeps one output copy even when a value occurs many times.

Can I use this VBA macro in Excel for the web?
No. Excel for the web does not run VBA. Use the formula alternative in a supported Excel version, or open the macro-enabled workbook in desktop Excel.

How do I get unique pairs instead of one combined list?
Keep the columns together and deduplicate rows using Data > Remove Duplicates, selecting the relevant columns. The result will be distinct row combinations rather than a single list of individual cell values.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.