Quick Way to Check for Duplicates in a Column
- 6 Comment
I have been using this quick trick lately to check for duplicates in a column of values. Let’s say you have a list of a large group of numbers (characters work as well) and want to see if you have more than one instance of any of the numbers. To check for that, one simple was is to use the CountIf formula.
I have already shown you how to use the formula in the SumIf and CountIf Foumulas post. But here I just wanted to mention this is an example where you can use it.
Let’s say we have a column, say column D, with 100 values that you want to confirm do not have any duplicates. To check this, in a column next to the data you are dealing with you can add this formula:
=countif($D$1:$D$100, D1)
Basically what this is saying is count how many times the value of D1 is in the column D for rows 1 to 100. The reason we had to lock the cells (the $ character locks the cells) is that we will need to drag down the formula to do our check. If anything other than 1′s appear in our check column, then we know that we have a duplicate item somewhere. Any easy way to look for the non-1 numbers is to filter the data and look in the drop-down filters available.
Here’s a quick screenshot of how the formula would work for you:
That was just a quick tip for today. There are numerous other ways to check for duplicate items in a column, such as using pivottables, but this is a quick and easy trick that you can start using today.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
More Excel Tips
6 Comments on this post
Trackbacks
-
ksarda said:
Here’s a question I see often. “I’m trying to use the RANK function to sort the values in Column A, but, the problem arises when I have two or more cells with the same value. Then, the RANK also repeats some positions. For example, if I have, 50, 60, 62, 62, 67 in Column A I would get 5,4,2,2,1 in Column B. Is there a way I can get 4,3,2,2,1 ?”
May 14th, 2009 at 11:42 am -
john said:
Hi ksarda – the rank function is actually doing the ranking correctly as there is no 3rd position in your example because of the tie for the 2nd position. You would have to write a custom function to rank the values in the manner you suggest. Hope that helps…
May 14th, 2009 at 12:16 pm -
Kaye said:
Thanks for the tip, this will be useful. Further, would CountIf work if I’m looking at hundreds of rows of customer order info and want to only have customer listed once with columns that include the various dates of orders. That way I don’t have to keep scrolling up and down…I don’t know that CountIf is the solution or not. Thank you.
August 26th, 2009 at 5:40 pm -
Anil said:
Hi ,
With regards to “Way to Check for Duplicates in a Column “..there is another way this can be done using the “Conditional Formating ” function. From the Formating menu select “Conditional Formating” in the “Conditions ” select ‘Formula Is ” Option and enter the formula =COUNTIF($A$1:$A$50,A1)>1. Then just select the format you want the duplicates to be displayed in .
Ensure that this formation is copied to all the other vertical cells you want to chek for duplicates.
December 20th, 2009 at 5:08 am -
iphone_addict said:
Thanks for the tip, I hope it works for columns that have strings instead of values. Will try this later.
July 15th, 2010 at 5:50 am -
Stu Cram said:
I also would like to determine ranks without skipping positions when there are ties, similar to the way ksarda mentioned earlier (May 14, 2009). If a custom function is needed, what would its code be? Hopefully someone can give an example of this.
July 3rd, 2011 at 9:44 am
