Use VBA to compare two lists and show the values in the first column that are missing from the second column. This is a simple way to compare two lists in Excel and summarize the results.

This list comparison macro is similar to the compare two columns for differences macro I created back in 2015, but this one specializes in reporting the missing data in the columns. It was submitted by wellsrPRO power user, Giancarlo, so we owe him a big thank you!

Macro to compare two lists in Excel

Created by Giancarlo

Sub Missing_in_the_second_list()
'Created by wellsrPRO member "Giancarlo"
'-------------------------------
 'USER INPUT
  Const Col_1 As String = "A"   'List:    one two three four five six"
  Const Col_2 As String = "B"   'List:    one three four six"
  Const Col_3 As String = "C"   'Return:  two five
  Const StartRow As Long = 1    'first row containing data
'-------------------------------
  LastRow_1 = Cells(Rows.Count, Col_1).End(xlUp).Row
  LastRow_2 = Cells(Rows.Count, Col_2).End(xlUp).Row
  Application.ScreenUpdating = False
  With Cells(StartRow, Col_3).Resize(LastRow_1 - StartRow + 1)
    .Value = Evaluate("IF(COUNTIF(" & Col_2 & StartRow & ":" & Col_2 & LastRow_2 & ", " & Col_1 & StartRow & ":" & Col_1 & LastRow_1 & "),""""," & Col_1 & StartRow & ":" & Col_1 & LastRow_1 & ")")
    On Error Resume Next
    .SpecialCells(xlBlanks).Delete xlShiftUp
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

How to use the VBA list comparison macro

To compare your two lists with this VBA code, you need to adjust the values stored in the four Const variables at the top. The Col_1 and Col_2 constants are the column letters where your two lists are. The third constant, Col_3, is where you want your summarized output to appear.

Any value in Column 1 (Col_1) that’s not in Column 2 (Col_2) will be printed out in Column 3 (Col_3). The comments in the USER INPUT block explain this really well.

The fourth and final variable, StartRow, is the row where your two lists begin. This macro inherently assumes your two lists are on the same sheet and they begin at the same row.

How the macro compares your two lists

Let’s pretend you have the word “one” entered in cell A1, “two” in A2, all the way to “six” in A6. This is your first list.

Now let’s pretend you have the word “one” entered in B1, “three” entered in B2, “four” entered in B3, and “six” entered in B4. This is your second list.

When you run the macro, the summary column reports the values in the first list that aren’t in the second list. You can see that the strings “two” and “five” are missing from the second list. Run the macro, and those words will appear in Column C.

VBA compare two lists
Results of VBA macro comparing two lists

The macro actually works by evaluating an Excel worksheet formula. It’s basically the same as highlighting cells C1:C6 and typing the Excel formula…

=IF(COUNTIF(B1:B6, A1:A6 ),"",A1:A6)

…then entering the formula as an array by pressing Ctrl-Shift-Enter. You’ll know you’ve done it right once you see the squiggly brackets { } appear around your formula.

Compare two lists with Excel Formula
Equivalent Excel Formula Results

Once the formula is evaluated, the macro cleans things up by deleting all the blank cells. What your left with is a compact list of all the items missing from the second list.

I ran this macro on a number of sample lists and it appears to work nicely. It does get bogged down a bit when comparing lists containing about 10,000 entries, but that’s to be expected.

I hope you enjoyed this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.