How to compare two lists and delete duplicate items in Excel

I like to post useful scripts that I write/modify/find online that I use in my everyday work. This is an Microsoft Excel macro that compares two lists of data and deletes duplicate items. I mainly us this Excel macro when I am compiling email lists for campaigns. A lot of times I have to creates lists with criteria like “Send to all unconverted leads in Salesforce, but exclude those who received emails from us in our prior two campaigns.” Querying the original list is easy, but a lot of times it is difficult to query any exceptions that are needed. Most times it is just easier to grab the lists that I know i want to exclude and compare my two lists and delete duplicate items. In this post I will include the Excel macro that you should use as well as give you a little background on how it works.


Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("Sheet1").Range("A1:A10").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet2").Range("A1:A3")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub


The script is very simple to use. Just open up Microsoft Excel and put your “Master” list in the first column of Sheet1. Then put the values that you want to delete from your “Master” list in the first column of Sheet2. Next modify the ranges of each of your lists (to make this easy I have highlighted these values in red). Then simply go to “View” -> “Macros” -> “Edit” -> and copy and paste the macro script. To run the macro, go to “Run” -> “Run Sub/UserForm” or just click the green run button. That’s is! It is that simple…