Filtering a List into Unique Records Using the COUNTIF Formula in Microsoft Excel

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.

Users are saying about us...

this formula with iF AND COUNTIF is excelent. The formulas: =A1=A2 in the same table, select FALSE in the criteria list of filter. =if(A1A2,0,1) is functional too select 0 in the criteria list NOTE. ordered data

The formulae immediately above in my reply to ‘jonny’ will give you a way to identify unique entries (count of 1), and if you dropped that ’1′ into column B, and then summed column B it would give a count of total unique entries.

However, I don’t understand what you mean by, “”based on other field in the adjcent rows””.

Please can you elaborate or give a short example of the data and result you want?

Your solution, corrected by Excel, gives me a #VALUE! error. I used {=SUM(((A2:A182)>=VALUE(“”1 Aug 2003″”))*((A1:A4)))} Is there more to the formula? I noticed you used >=VALUE in your formula. I’m trying to delimit the date range from 1 Aug 2003 through 15 Aug 2003. Shouldn’t <=VALUE be included somewhere as well? Also, I'm using the 08/01/03 date format. Does that make any difference?"

“Oooops! I noticed an error in the formula and thought I had it fixed by using {=SUM(((A2:A182)>=VALUE(“”1 Aug 2003″”))*((A2:A182)))}
It returned 6850537! This, of course, is incorrect. Even if every date in that column were between 1 Aug through 15 Aug, it cannot be any higher than 181, since I’m only using 181 cells. (Manually counting, the correct answer should be 101.) Help!”

Thanks for all your help thus far. I’m sure you have other things to do besides answering my silly little questions. As I stated in my original post from Aug 15, “”I need to count the number of times that the dates from Aug 1, 2003 THROUGH Aug 15, 2003 occur in a column.”” To answer your question above, no, I’m not counting “”y””s. I need to count the NUMBER of dates from 08/01/03 to and including 08/15/03. COUNTIF works beautifully if I only want to start counting from 1 Aug 2003 and include every date on up. For instance, if I use =COUNTIF(A2:A182,””>=1 AUG 2003″”) I get 181 returned. However, the delimiting part <=15 AUG 2003 is still a mystery. Where does that go in the equation? Do you know? I've tried various versions of nested functions, but I keep getting the ""Too many arguments"" error message. Ugh!"

I am trying to create a spreadsheet which contains names of people on column C. However, as I enter names into the cells in column C, I would like excel to give me an error if the name being entered already exists in another cell in Column C. Anyone know how to do that?

“I have an athletic spreadsheet displaying the name and result of a particular match. I want to be able to Auto Filter the names to easily read the results. That’s easy…

Now I want to also be able to count the “”Wins”” and “”Losses”” of that particular athlete without adding up all of the others stats.

The Auto Filter just simply “”hides”” the others names when I choose a particular athlete so when I do a COUNTIF, it counts every name rather than the filtered name. Is there a secret COUNTIF IF statement that I can embed?”

Let me be more specific to make it easier. There will be multiple entries for any single athlete, but the result will obviously change depending on success.
Names are in column B and Results are in Column C
John Smith W
Jack Johnson L
Pete Anderson W
John Smith L
John Smith W
Pete Anderson L

I want to add up the records in Cell A2 (with a CONCATENATE command) by a dropdown list of athletes names that I’ve created with everyone’s name (spelled correctly) in Cell A1.

Cell A1 will have a dropdown of
John Smith
Jack Johnson
Pete Anderson
etc…

Cell A2 will have
Wins 2 (or whatever)

Cell A3 will have
Losses 2 (copy of A2 referencing losses)

I really appreciate all of your help Alan. If you would also email me directly at ryan_molitor@hotmail.com, that would also help considerably…”

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.

Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

You can adjust all of your cookie settings by navigating the tabs on the left hand side.

Strictly Necessary Cookies

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

disable

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.