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.

SCRIPT FOR MICROSOFT EXCEL MACRO

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
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

HOW THE MICROSOFT EXCEL MACRO WORKS

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…

5 thoughts on “How to compare two lists and delete duplicate items in Excel”

  1. I copied your macro script and changed the cell ranges as you instructed. When the Macro is run I get a Compile error on the row:
    iListCount = Sheets(”Sheet1“).Range(”A1:A43“).Rows.Count
    It is expecting a ‘list separator or )’ where it is finding the colon between A1 and A43.
    Any suggestions?

  2. Guys, I updated the script. Can you try again and let me know if you still get an error? If you still get that error, the more information you can provide me to help me debug would be great.

  3. Hi there,

    Thanks for posting thing… My base list is about 120K and I want to exclude any emails found from another list of about 92K. I suspect about 40% are duplicates.

    When i run the macro, excel freezes. When i tested your script on a sample set it did work – is there another way I could do the same thing without running a macro? It appears that scripts i try and run with these lists doesn’t work, likely due to the list size. Any suggestions?

    1. Hi Robb,

      I haven’t tested it on lists that size. Can you possibly break down your lists into smaller segments? I know it would be tedious to do that, but better than doing it manually :) I’d also give you a good idea of where the limit is in Excel for running a Macro.

      I’m not sure of any other ways to do this…

      Andrew

Leave a Reply

Your email address will not be published. Required fields are marked *