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. SUMPRODUCT assigns a value of 1 when the criteria are met, or a value of 0 when the criteria are not met.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
May 2023
Categories |