The formula becomes applicable for the rest of the cells in the Output column after we fill down the first cell.ĥ.Finally, the IF function looks for the sum less than or equal to 1 and returns a blank cell, and if not found then it returns the defined text ‘Duplicate’.=SUM((–EXACT($B$5:$B$14, B5)))
The SUM function then sums up all the numeric values found in the preceding step.With the use of double-unary (–), the return values convert into numbers, ‘1’ for TRUE and ‘0’ for FALSE.The EXACT function here looks for the case-sensitive and exact matches for the first text in the Name column and thereby returns the following output:.But the formula has returned ‘Duplicate’ for the first two occurrences only and the third one is ignored since its first letter case does not match with the other ones. If you notice, you’ll find that the name ‘Fred’ is present thrice in the Name column. Now press Enter and autofill the entire column to find all return values. In the first output Cell C5, the combined formula with the referred functions will be: The SUM function simply sums the numerical values. The EXACT function checks whether two strings are exactly the same. We’re going to combine the IF, SUM, and EXACT functions here. Now we’ll apply another combined formula to find the case-sensitive duplicates. Excel Formula to Find Case-Sensitive Duplicates in a Single Column So, the 1st occurrence of any text in the Name column is not counted for more than 1 here.Ĥ.
While dragging down the Fill Handle to find the next outputs, the number of cells in the defined range for the COUNTIF function increases by 1 for each successive cell. =IF(COUNTIF($B$5:$B5,B5)>1,"Duplicate","")Īfter pressing Enter and dragging down to the last cell in the Output column, we’ll get the following return values.įor the first output in Cell C5, we’ve defined the cell range with $B$5:$B5 only, and therefore, the formula will look for the first cell only to find a duplicate value.
0 kommentar(er)
