Tuesday 8 April 2014

Find duplicate value in excel through Countif


Find duplicate value in excel through Countif

In latest versions of MS Excel finding duplicate values is very easy by selecting desire column/ range and go to conditional formatting and select duplicate values.

But today I am going to tell you other way to find duplicate values, also it will tell you number of repetition of a value.
If value is single it will display “1” if value is repeat two times it show “2” and number will increased as repetition increased.
It performed with a very simple formula, lets demonstrate it.

Syntax of “countif” formula is

Countif(range, criteria)

Range:            range is a specific row or column/columns, which need to be check for count
Criteria:            Criteria is what to be count.


Now I am going to use same formula to find all duplicate values.

Here I have a sheet with numbers which have multiple values.

In “A2” write formula

=countif($b$2:b2,b2)
Explaination:
“b2:b2” as range and make first value absolute by adding “$” like “$b$2”.
second will not as absolute in range.
Criteria is also “b2”
By doing this it will search in range b2 to b2 and count b2 it will give one.
By copying formula in entire column it will change as
=countif($B$2:B3,B3) and so on.
It means it will search range b2 to b3 and count b3 value in range.

you can also add a simple conditional formatting for values more than one “1” will highlighted and it will ease to find duplicates.
and values greater than 1 will be highlighted red, which means value repeat in 2, 3,4 etc. times.








No comments:

Post a Comment