Remove Duplicates From Data List

Duplicates are very common in not only Excel but also databases. And the one of the easiest ways to work the data is through Excel. During our Advanced Excel Course, users always asked us how to remove these records in the database. And one of the most common problems is having muliple instance of the same email address because the client share one email address or having the same mailing address because your clients are staying in the same house. So imagine when you are

sending emails or printed materials to all your customers, your customers’ inbox or letter boxes will have a few copies of the same emails or printed materials from you. Not only it is a waste of resources, it also does not reflect very well on your company too.

There are 2 ways to remove duplicates in Excel. The first way is by using Advanced Filter. This method is very effective if 2 records in your database is exactly the same, meaning the content in all the fields of one record is the exactly the same as another. Should one of the fields differs, then the record is considered different and will not be filtered out. First, let me show you what I mean by that.

You have a list of email addresses. In the list, the last 2 records are repeats of the first 2 records. And you want to remove them using advanced filter. And here is what you do.

1. Go to the menu and click on Data -> Filter -> Advanced Filter.

2. Select the option to copy the results into another location.

3. Select the range of the list. In our case, it is A1 to A7, including the header (A must!).

4. In the box for Copy to: select a cell away from the list e.g. cell F1

5. Make sure that the box next to “unique records only” is checked.

6. Click Ok and the results in cell F1 will show a list without the last 2 emails which are duplicates.

Now if you have a list containing Emails and Names as shown:

Using Advanced Filter, you will get a list with 5 records instead of 4 in the previous example. This is because the names for the email address [email protected] (A2 and A6) are different (Jason and Jason Khoo). As such, they are recognised as 2 different records.

To remove multiple instances of the same email address from such a list, you have to use the 2nd method, i.e. the IF formula method. Here are the steps:

• Sort the list by emails.
• In column C, enter the IF formula to compare the email address in the current row with that contained in the previous row. If the email address is the same, i.e. A3 is the same as A2, the formula will return the word “duplicate” in cell C3. If they are different (A3 is different from A2, return a blank – denoted by double inverted comma “”)
• Copy the formula down.

• Record 4 and 7 will show up as duplicate. In this example, record 4 is also marked even though the Names (in B3 and B4) are different.
• Activate auto-filter and select blanks as the filter criteria for column C.

• All the emails will appear only one time in the filtered list.
• Copy and paste records to a new worksheet.
• You have a list of emails and names without duplicates in it. Even if you have a list that contains many fields, this method will surely work for you.