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.

### 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

Any value in Column 1 (

The fourth and final variable,

### 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.

**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.

**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

*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.*