Use Advanced Filter to get rid of duplicates in your spreadsheet

Having duplicate rows or entries in your spreadsheet can drive you up the wall. It could be that you need to clean up sloppy contact info from your address book, or maybe you need to consolidate several tables into one. There are a lot of possible scenarios where the Advanced Filter tool is the solution for getting rid of duplicates in your spreadsheet.

Advanced Filter

Here is a sample problem.

Each column has duplicate entries.

The first column shows row numbers. The second column shows names. The third column shows email addresses. The fourth column contains dates. The fifth column contains text.

  A B C D
1 Name Email Date Issue
2 Walter blysky@gmail.com 10/1/2012 Health Care Reform
3 Tyrion theimp@gmail.com 10/1/2012 Health Care Reform
4 Dan dandority@gmail.com 10/1/2012 Health Care Reform
5 Glenn go-to-town@gmail.com 10/1/2012 Health Care Reform
6 Tyrion theimp@gmail.com 9/24/2012 Foreclosures
7 Glenn go-to-town@gmail.com 9/24/2012 Foreclosures
8 Kalinda investigator@gmail.com 9/24/2012 Foreclosures
9 Walter blysky@gmail.com 9/24/2012 Foreclosures
10 Glenn go-to-town@gmail.com 9/17/2012 Schools
11 Dan dandority@gmail.com 9/17/2012 Schools
12 Tyrion theimp@gmail.com 9/17/2012 Schools
13 Tyrion theimp@gmail.com 9/10/2012 Immigration Reform
14 Tyrion theimp@gmail.com 9/3/2012 Fracking

To start, we will want to select a column that allows us to distinguish between unique and duplicate entries. Then we can filter the rest of the data set by what is in that column. Email addresses should work fine for this example, so column B is a good choice.

Navigate to the pull-down menu, Data.

Then, find Filter and expand the menu so that we can select Advanced Filter…

This image is screenshot of Excel following the instructions listed to reach the Advanced Filter function.

dedupe02

A new window should have popped up.

Select the following options:
  • Copy to another location
  • List range: (this should be automatically filled in, cancel and start again if it is not)
  • Criteria range: (ignore)
  • Copy to: (click on the make-a-selection icon to select the location where you want to copy Column A. I’ll pick the first cell in column F, F1.)

This image is screenshot of Excel following the instructions listed to paste only single instances of all the values found in the list range.

dedupe03

Column F now contains only unique values copied and pasted from Column B.

More about this table.

The first column shows row numbers. The second column shows names. The third column shows email addresses. The fourth column contains dates. The fifth column contains text. The sixth column is purposely left empty and represents a break from the data set on the left and the single column to the right. The seventh column contains email addresses.

  A B C D E F
1 Name Email Date Issue   Email
2 Walter blysky@gmail.com 10/1/2012 Health Care Reform   blysky@gmail.com
3 Tyrion theimp@gmail.com 10/1/2012 Health Care Reform   theimp@gmail.com
4 Dan dandority@gmail.com 10/1/2012 Health Care Reform   dandority@gmail.com
5 Glenn go-to-town@gmail.com 10/1/2012 Health Care Reform   go-to-town@gmail.com
6 Tyrion theimp@gmail.com 9/24/2012 Foreclosures   investigator@gmail.com
7 Glenn go-to-town@gmail.com 9/24/2012 Foreclosures    
8 Kalinda investigator@gmail.com 9/24/2012 Foreclosures    
9 Walter blysky@gmail.com 9/24/2012 Foreclosures    
10 Glenn go-to-town@gmail.com 9/17/2012 Schools    
11 Dan dandority@gmail.com 9/17/2012 Schools    
12 Tyrion theimp@gmail.com 9/17/2012 Schools    
13 Tyrion theimp@gmail.com 9/10/2012 Immigration Reform    
14 Tyrion theimp@gmail.com 9/3/2012 Fracking    

Now that we have a new column purged of duplicates, we can use it as the basis for a new cleaned-up table. We would simply have to think about how to lay everything out, decide what to keep, and then selectively merge data.

Was this easy?

Did this tutorial work for you? Was it exactly what you needed? Confusing? Did I leave something out?

Spread the love! Ask questions, leave a comment, or just share this with someone who would really benefit. What other spreadsheet tutorials would you like to see?

If you find yourself in a pinch, you should know that I am available to help with spreadsheet projects.

Leave a Reply

Your email address will not be published. Required fields are marked *