Checking for duplicates in Excel

A spreadsheet technique using IF formulas to identify duplicate jury duty summons by comparing last names, first names, and street addresses, allowing manual review to remove true duplicates while preserving legitimate multiple records.

To find duplicates in the list of people called to jury duty, I sorted the list by last name, first name, and street name. Then I ran an "=IF" test on all three to see if the value in one cell was the same as in the cell below it.

This statement shows you if the last name is duplicated in the cell below for the spreadsheet pictured here:

(A2=B2, dup",

If there is a duplication, Excel inserts the word "dup" the cell. I calculated a similar formula for first names and streets. Then by filtering I could find the triple "dups" and delete them, leaving only one record for the person. I checked double "dups" and if they appeared to be true duplicates, I deleted them.

In this example Carolyn Messenger is a triple "dup." She was initially summoned in March 2002 and then summoned again two months later. That means she postponed her first jury duty summons, because you can't serve twice in a year. I deleted that row.

Lynda Smith has two addresses. I can't know if she's a duplicate from this list, or if there are two Lynda Smiths, so I let both records stay.

Robert Sommer shows that it's important to use first names too. There are two Sommers from Galway Drive, but one's Robert and one's Roselyn. Both stayed.