I recently had a Master List of email addresses, and another list of Reject email addresses that needed to be excluded from this (because we no longer wanted to email these people).
Before I removed them from the mailing software, I wanted to see how many duplicates were in the list. (I didn’t want to accidentally wipe out the list by being over-zealous with the rejects!)
To do this, I used the COUNTIF function in Excel. (I’m using Excel 2016 for Mac, but this should work in any modern version of Excel.) This is much safer than a VLOOKUP for simple work. (See this article for the dangers of VLOOKUP.)
The screenshot below shows my spreadsheet. I replaced the email addresses with simple letters here, to make it clearer to see what’s going on.
Find duplicate values in Excel
I created three columns, for my Master List, my Reject List, and whether this row in the Reject List contains a duplicate.
The basic formula, as you can see in the screenshot below, is then:
COUNTIF(Master-List-Range, Row-From-Reject_List)
At the bottom I just did an Auto-sum. In this example, 3 rows are duplicates.