Do leading zeroes affect "duplicate flag" formulas?


I have a sheet with a column of the 'Text/Number' type called "ID Number." My intent is to have another column of the 'Checkbox' type (called "Flag Duplicate") to show at a glance that an "ID Number" has been duplicated (the data in the "ID Number" row should be unique in each cell within the column).

I used a column formula for "Flag Duplicate" which initially seemed to work:

=IF(COUNTIF([ID Number]:[ID Number], [ID Number]@row) > 1, 1)

If I manually punch in a number in the "ID Number" cell on, say Row 1 and then do the same for the corresponding cell for Row 2, it works. Both "Flag Duplicate" cells in each row show a flag...UNLESS...the ID Number has a leading zero. And I have a lot of ID numbers with leading zeros. :(

Is there some alteration to the formula that needs to take place in order for this to flag duplicate ID Numbers correctly? Or maybe some alteration to the column type?



Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!