Rahul has asked us about the way to find duplicate values in Excel by way of using formula.
Today, we are going to look into a formula known as countif(). What the formula does is return the number of occurrence of a value (our criteria)in a range.
In its simplest form, the Countif() is used as
=countif(range, criteria)
where
count: cells where you want to look in
criteria: what you want to look in
for example
in the following table, if we want to check the occurrence of “Ram” in the range A1:A10
we will use the formula as
=COUNTIF(A2:A10,”Ram”)
the result will be 2, since “Ram” word occurs two times in the range A2:A10. Make the range A2:A10 as absolute range and writing the formula as $A$2:$A$10 and copy the formula down to E10, the result will be
As you can see cells E2 and E6 both return the values as 2 as the word “RAM” occurs 2 times in the range. This is the simplest use of Counif() function to return the occurrence. Now let us look at the more refined way of using the function.
COUNTIF(): A refined usage
Lets modify our data so that the word “Ram” occurs 5 times in the data.
Now, we modify our approach to finding occurrence step by step so that E2 will have the value as 1, E4 as 2, down to E10 as 5.
what if we want to check the occurrence of A2 in the range A2:A2, A3 in the range A2:A3, A4 in the range A2:A4, A5 in the range A2:A5, A6 in the range A2:A6, A7 in the range A2:A7, A8 in the range A2:A8, A9 in the range A2:A9, A10 in the range A2:A10.
Notice that fist cell in the Range if fixed to A2 so we enter our formula in cell F2 as
=COUNTIF($A$2:A2,A2)
and copy it down to F10 to give the result as
Now you see how beautiful the result has come. We have the first occurrence, then second, third and so on. We have just arrived at the solution where we want the occurrence of each item rather than count of the value in whole range.
Tip : If you want to remove the duplicates, filter the data on column F and delete the rows where the value in column F not equal to 1.
This was the short and crisp article showing the ways to find occurrence and count of duplicate values. I hope you find this article helpful. You will now be able to apply this in your day to day work life such as finding duplicate account number or a cheque number in banking domain or duplicate customer IDs.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the LAMA (Learn at my Academy) will be highly appreciable
We are always concerned about your preferences.
Excellent post!