How to Find Trace Copy or Count Duplicates in Microsoft Excel Using Formula

Hi Friends,

The beauty of Microsoft Excel is that if you think logically then it is possible in Excel. Today we will discuss about such a topic which will make you realize the beauty of Excel.

In my last article I have discussed about how to identify Duplicate or Unique values in Microsoft Excel using Excel's inbuilt tools like conditional formatting. Hope you have got enough knowledge about Conditional Formatting and about Duplicates in Excel.

Today we will continue discussing about Duplicates in Excel but using Formula. We will discuss about

1.   How to Count Duplicates or Unique values Using Formula and then

2.   We will copy or pick or collect the Duplicate or Unique value using Formula

So lets' start. For easy understanding we'll list it step by step.

Find Duplicate or Unique Using Excel Formula

Please have a look at the above picture where light yellow highlighted colored cells contains Duplicate values which I highlighted using Conditional Formatting. Now we need to fill the next four columns using formulas.

The beauty of Excel is that you can do any logical things using proper functions and nesting them with each other.

How to Count Duplicates or Unique values Using Formula

1.   We know COUNTIF function counts specific value or character within a specified range. So we can use this function to count how many times one specific number is appearing in a given range.

2.   The syntax of COUNTIF function is COUNTIF(range, criteria), in our example this formula becomes =COUNTIF($C$3:$C$16,C3) as shown in the below image.

3.   The trick is we provide a range (here it is C3:C16) and put dollar sign to fridge it by pressing F4 button and then we provide the number we are looking for Duplicates (here it is "C3").

4.   If the output of COUNTIF function is more than one then this number has duplicates in the given range else no duplicate value is present.

Find & Count Duplicate or Unique Using Excel Formula

5.   Now if you want to write the word 'Duplicate' against duplicate and 'Unique' against unique value in a data range, we just need to update the formula using IF function as shown in the below picture.

the formula is : =IF(COUNTIF($C$3:$C$16,C3)>1,"Duplicate","Unique")

Identify Duplicate or Unique Using Excel Formula

So this is the trick to find the duplicate values using formula, nesting of IF and COUNTIF function now we will move to the second part of this article.

How to Copy or Pick or Collect the Duplicate or Unique values using Formula

This part is also same as the above method or trick, what we need to change is that we need to make a nested formula using IF & COUNTIF function as above example.

Now the tweak is put a conditional checking part to judge whether the count of number is more than one or not. If the count of the provided number is more than one, then we need to print this number if we need duplicate value by referring the cell (C3 for example in the formula =IF(COUNTIF($C$3:$C$16,C3)>1,C3,"") in the value if true part of IF function and one pair of double quotes to print nothing if this number is unique. Please refer to the below image for further clarification.

Identify and Copy Duplicate or Unique Values Using Excel Formula

The reverse is the case if you want to get the data for Unique value.

I hope you have enjoyed this article and realize the beauty of  Nesting in excel.

Identify and Copy Duplicate or Unique Values Using Excel Formula

Keep reading for new ideas in Excel...

Thank you for reading...