Find Duplicates and Triplicates in Excel

Source from:http://exceliseasy.wordpress.com/2012/11/12/find-duplicates-and-triplicates-in-excel/

Select the cells you want to check and choose Highlight Cell Rules => Duplicate Values from the Home Ribbon.

Then, the easy way

If you only want to locate the duplicates, the super-easy way is the right way to do it. But let’s say you want to find triplicates or quadruplicates, i.e. three or four occurrences of the same piece of data. There is no built-in feature for that, so we have to find our own way.

In my example I have 27 rows of data, with names in the range A2 to A28. In A2 we find the name Robert, so if we want to find out how many times Robert appears in the list, this is the formula: =COUNTIF($A$2:$A$28,A2). We’ll use Conditional Formatting with a formula like this.

As we’ve seen in a previous post, Conditional Formatting requires a TRUE or FALSE. Let’s see how our formula works when we put it in the worksheet. We use the same formula as above, only with “=1”, “=2” or “=3” in the end, and we will get TRUE or FALSE for each statement:

So, let’s put this formula into Conditional Formatting, with one small adjustment: Instead of hard-coding the value after the equal sign (1,2,3 etc.) we’ll use a cell reference. I will have my reference in cell E2.

Select the cells you want to include in the search (A2:A28 in this example) and click on Conditional Formatting from the Home ribbon and choose New Rule (or shortcut: Alt => H => L => N). Choose “Use a formula to determine which cells to format” and type the formula into the formula field:

=COUNTIF($A$2:$A$28,A2)=$E$2

Note that the range A2:A28 and the reference to E2 (number of occurrences) have to be locked with dollar signs (shortcut: F4).

The result: All the triplicates are highlighted. If you change the value in E2 to 2, you will get the duplicates instead, and if you change it to 1, only the unique values will be highlighted.

Extra: Do you want to learn how to create a search field in your Excel report? Have a look at this post (opens in new window/tab): Create a search field in Excel in 5 minutes

Note: If you use comma as the decimal separator as default (applies to most non-English users) you have to replace the commas in the formulas with semicolons.