If you have a range of values which contain duplicates, we have an interesting way of filtering for these duplicate values. This is one of the ways of obtaining a list of duplicates and then either highlighting or deleting them as the need may be.
How to use excel functions to find the duplicates in data?
So first we will identify the duplicate rows. There are many ways of doing this and this is just one of the methods to identify using formula.
The formula we need to use is –
You will observe that in the formula =IF(COUNTIF($A$2:A2,A2)=1,1,0),
In the highlighted section the first instance of A2 is shown as $A$2 and the other is just A2. Well, we want to count from the beginning of the range which is A2 going down one row each time. So that’s why the 1st instance is absolute and the next is relative. This way, when you drag the formula down, from B2 to B3, the formula will change to =IF(COUNTIF($A$2:A3,A3)=1,1,0) and so on.
The 2nd parameter A2 (highlighted in green) is the criteria for the countif function.
So we compare if the count = 1, if yes, show 1 in column B for that row, else show 0. So if the count is more than 1 as in the case of duplicates, it will show 0 here.
This is our data set before applying the formula –
So after applying this formula in the entire column, we get the following results –
Now we find that all the unique values are marked as 1 and the duplicates are marked as 0 in column B. So if we need the unique values, we need to apply the filter to the columns by clicking on the Data menu and click on Filter in the Sort & Filter group.
The filter has been applied –
Then filter by 1 in column B and highlight it or copy it to another sheet as per the requirement.
If we want the duplicate values to be highlighted or deleted, we need to filter by 0 –
You can see all the values being filtered. Now you can either delete these rows as they are duplicates or color them, depending on your need.
So this is how a formula helps us in identifying duplicates and then taking the required action on them.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.