In column B the highlighting is made using the SUMPRODUCT formula, while cells from column E are highlighted using the built-in Excel conditional formatting. They will have different IDs but the same name. This can be the case when two people with the same name register on a website or if two employees have the same name. In this example, I mentioned that there are some entries that have the same name, but the ID is different. When you apply the new rule, it will highlight all the cells that match the result of our formula. The result of our two Conditional Formatting rules Note: Keep in mind that all the arrays in the SUMPRODUCT formula must be the same size. Otherwise, SUMPRODUCT will generate a #VALUE! error.
If the value is greater than 1, this means that the combination of ID + Full name is not unique in our table. ĭ) The formula will check if the result returned by SUMPRODUCT is greater than 1 (remember our ">1" condition). The conditional formatting formula will then move to the next set of cells, B4 and E4, and perform the same checks, then continue all the way down to row 202.Ī simple way of looking at this formula would be:Ī) SUMPRODUCT will check if ID from cell B3 is found in the array $B$3:$B$202 and generates an array with only 1s and 0s. Part three checks if the sum resulted from SUMPRODUCT is greater than 1. If they are both true, then it will return a value of 1. Part one and two checks to see if cells from arrays ($B$3:$B$202) and ($E$3:$E$202) match the values from cells B3 and E3 respectively.
The easiest way to understand how to find duplicates in Excel with this conditional formatting formula is to split it into three parts. Understanding the Conditional Formatting formula Using "*" sign in the formula makes the two conditions cumulative. Then, it sums all the values and returns the total. Step 4: Save your excel file as “Excel Macro-Enabled Workbook” *.SUMPRODUCT assigns a value of 1 when the criteria are met, or a value of 0 when the criteria are not met. Step 3: Select “removeDuplicates”, you can see a list of macros available in your workbook Step 2: Right-click on “VBA Remove Duplicates” and “Assign Macro.” Step 1: Add a shape ( VBA Remove Duplicates) to your worksheet Reset “nonDuplicate” variable 'reset nonDuplicate to Trueįollow the below steps to remove duplicates using Excel VBA: “nonDuplicate” is True then place to column B and increase uNo by 1 'if nonDuplicate is true, place cell value in column B and increase uNo = uNo + 1Ĭells(uNo + 1, 2).Value = Cells(colA, 1).Value If Cells(colA, 1).Value = Cells(colB, 2).Value Then Assign False to the “nonDuplicate” variable. If the number is already placed in column B. Take each number from Column A and check with Column B (unique range) 'Use for loop to check each number from A2 to A15 Since the first number is already placed in cell B1, Loop starts from A2 to A15. Initialize variables: 'Initialize uNo = 1 since first number is already placed in column B Assign True to the variable nonDuplicate uNo Integer Count no of Unique items in column B colA Integer Iteration column A cells colB Integer Iteration column B cells 'Variable Declarationsĭim nonDuplicate As Boolean, uNo As Integer, colA As Integer, colB As IntegerĪlways first value will be unique, So A1 place to cell B1 'Place first value to B1 How to Convert Data from Wide to Long Format in Excel?ĭeclare Variables: Variables Data Type Comments nonDuplicate Boolean It is a Boolean value (True/False).