How Do I Find Duplicates in Excel?

Got a list of values in an Excel Spreadsheet and need to know if there are any duplicates?

I’ll give you two easy options here.

Method 1:
OriginalList

This is our sample list.  Notice that we’ve got two Aardvarks.

 

 

Method1Formula
In a blank column next to our data (add in an extra column if you have to, use an if statement to compare A1 to A2.

=if(A1=A2,1,0)

This formula returns 1 if the two values are the same, and 0 if they aren’t.

EXCEL 3/31/2017 , 8:17:30 PM Microsoft Excel - Book1
After you hit <Enter> on the formula, it returns a 0 because Aardvark and Bear don’t match.  We need the same formula all the way down, so double click the tiny little black square shown here to replicate it.

 

ReplicateFormula2
Now the formula continues all the way to the bottom.  But it hasn’t returned any matches.  What gives?  The problem is that this formula only works if the matches are right next to one another.

So highlight the list and click the sort button.SortButton
Method1SortedList

Now that the list is sorted, you get a 1 next to the first duplicated value.  If you like, you can use the autosum button at the bottom of the column to get a count on the number of duplicates.AutoSumButton

 

That’s great.  It’s a nice easy count of the number of duplicates.  The logic even runs really fast if you’ve got a 30,000 row spreadsheet.

But…  what if you can’t resort the spreadsheet for some reason?

Method 2:

OriginalListMethod2Formula
Here’s our same list, but since we can’t resort it, we’ll use the CountIf
function:

=CountIf($A$1:$A$7,A1)
Don’t forget the dollar signs on this formula.  They tell Excel that you don’t want those values changed when you replicate the formula all the way down.

ReplicateFormula1_2

You can already see that this version found two Aardvarks even though they aren’t next to one another.  Just like the previous example, double click the little black square to replicate the formula.
ReplicateFormula2_2

Now you can see that it gives you a count of how many times each value appears.  Anything over a one is a duplicate.   You can’t use AutoSum to find the number of duplicates again, so you might consider using CountIf again to get the duplicates:

 

In B9 enter this formula:

=CountIf(B1:B7,”>1″)

In our example, Excel will return a 2.  That number is a little inflated, but at least it tells you that you’ve got some duplication to look for.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s