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_1 & ", " & 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

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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. Send a big thanks to Giancarlo in the comments section if you found it helpful.

After that, please share this article with your friends on Facebook, Twitter, and Google+ and subscribe to my free wellsrPRO VBA Training Program.


About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.