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 Read more: Find Duplicates in Two Columns in ExcelĤ.
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.