Authored by Vince Russell on 26 July 2005 at 02:59PM
Current rating: 2.5 out of 5 stars!
Filed under: Excel Tips

Removing duplicates values using Excel

Excel is proper great!

When I say you can remove duplicates using Microsoft Excel, I'm not talking about creating a spreadsheet in order to launch a worldwide campaign to eradicate Vince's, I mean you can use it to remove duplicate values from a list.

Let's imagine you want to send out your latest bit of spam (sorry, newsletter!) to a list of email addresses that you've got stored in Excel. You can use a combination of sorting and the IF statement to remove any duplicates within the list.

Lets assume that you have three columns in your spreadsheet, email address, first name and surname, and you want to remove all duplicate email addresses from the list. First thing we need to do is to sort the list by email address using the sort command from the menu (Data | Sort)

This will group all the duplicate email address together like so…

wov@worldofvince.com wov@worldofvince.com fred@bigemailaddress.co.uk fred@bigemailaddress.co.uk fred@bigemailaddress.co.uk la_la_la@yippee_brilliant.org la_la_la@yippee_brilliant.org

Now that all the duplicate addresses are together, we can use the IF function to check to see which are duplicates. As the data is sorted, expressing what we need to do in English gives us…

Is the email address on this row the same as the previous email address True = Duplicate False = Not a duplicate

If we insert a column in our spreadsheet and type the following formula in the second row...

IF(A2=A1, "Duplicate", "Not a duplicate")

So now we have correctly identified our duplicates, if we need to remove them, we first have to remove formulas from Excel, then we can sort by our new column and delete the ones marked as duplicates!

If we want to check more value at a time (for example, first name and surname), we can use IF with the AND function...

IF(AND(B2=B1, C2=C1), "Duplicate", "Not a duplicate")

And that's it! No more duplicates! Now if only this worked on the UK pop music scene, half the top twenty would be flagged as a duplicate - yes McFly, I'm talking to you!

Download File Download the example Excel file

See also
Removing formulas from Excel