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 firstname.lastname@example.org (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:
Share this page:
Would you prefer to share this page with others by linking to it?
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft