The COUNTIF function helps count cells in a range that satisfy a given condition. We can then delete these visible rows and remove the filter to obtain rows containing only unique product names. The result is the set of all duplicate rows. Then, we will use this result to filter out those rows that occur for the second time or more. From the Developer tab, select Visual Basic.įirst, we will use the COUNTIF function to count the first occurrence of a product as 1, its second occurrence as 2, and so on.To enter the above code, copy it and paste it in your developer window. Note: You cannot undo changes made by this VBA script, so we suggest you keep a backup copy of your dataset before running the code.
#EXCEL FIND DUPLICATES IN ANOTHER COLUMN CODE#
The code then removes all the rows from the range that contain duplicate product names. In the above code, we specified this as Column 1 or the Product Names column. It takes the selected range, as well as the columns that you want to base the duplicate removal on. This code uses a VBA built-in command for removing duplicates in list-objects. Rng.RemoveDuplicates Columns:=Array(1), Header:=xlYes If you are comfortable with a little coding, then you can use this method to remove duplicate rows based on a single column.Įven if you’re not that keen on coding, you can just copy-paste the following code: Sub Delete_duplicate_rows() So instead, you can have the code and add it to the Quick Access Toolbar, so that you can access it with a single click. This method is useful when you have to do this quite often and don’t want to follow many steps. If you want to highlight all the recurrences of a value (both duplicates, triplicates, quadruplicates etc.You can do this easily using a short VBA code as well. If you change the value in E2 to 2, you will get the duplicates instead, and if you change it to 1, all the unique values will be highlighted. The result: All the triplicates are highlighted. Note that the range A2:A28 and the reference to E2 (number of occurrences) have to be locked with dollar signs (shortcut: F4). Select “Use a formula to determine which cells to format” and type this formula into the formula field: Select the cells you want to include in the search (A2:A28 in this example), go to the Home Ribbon and choose Conditional Formatting > New Rule. So, let’s put the formula into Conditional Formatting, with one small adjustment: Instead of hard-coding the value after the equal sign (1,2,3 etc.) we’ll use a cell reference. In this example, for Robert, the statement is true for “=3”. We use the same formula as above, only with “=1”, “=2” or “=3” in the end, and we will get TRUE or FALSE for each statement. First, let’s see how our formula works when we put it in the worksheet. We will use almost the same formula in Conditional Formatting: As you might already know, Conditional Formatting uses Boolean logic, which means that it checks whether or not a statement is TRUE, and formats the cells that return TRUE. In this example the formula will return 3. In A2 we find the name Robert, so if we want to find out how many times Robert appears in the list, we can use this formula: =COUNTIF($A$2:$A$28,A2). In my example below I have 27 rows of data, with names in the range A2 to A28.
#EXCEL FIND DUPLICATES IN ANOTHER COLUMN HOW TO#
two, three or four occurrences of the same piece of data, we need another approach: Conditional Formatting with a formula.įirst, let’s find out how to count the number of occurences in a list. But sometimes we want to make it more dynamic: If we want to be able to choose between highlightning duplicates, triplicates or quadruplicates, i.e. If you only want to locate duplicates, the super-easy way above is the right way to do it. Select the cells you want to check, go to the Home Ribbon, choose Conditional Formatting and select Highlight Cell Rules > Duplicate Values.
0 kommentar(er)
