Issue with leading zeroes when Identifying duplicates

cnikkih
cnikkih ✭✭✭✭
edited 12/09/19 in Formulas and Functions

I am using the following formula to identify duplicate invoice #s in a spreadsheet. I've noticed that the formula does not work when the invoice # starts with a zero. It works for all other formats: text, numbers (without leading zeroes), dates. The column property is set to text/number. What could be the issue??

=IF(COUNTIF([Invoice ID#]:[Invoice ID#], [Invoice ID#]85) > 1, 1, 0)

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 10/23/19

    Hello,

    I was able to duplicate the issue you are experiencing. Numbers with 0 in front are actually recognized as text, since the grid automatically puts a ' mark in front of the zero to keep it there (you can read about this, here). 



    That said, a COUNTIF formula should work on text, too, so this is unexpected behaviour. I have created a case on your behalf with our Support team and will notify you once we have more information. 

    In the meantime, one alternate solution would be to create a helper column that converts your data into numbers by using the following formula:

    =VALUE([Invoice ID#]@row)



    If you then direct your IF(COUNTIF formula to reference this column, it should work. 

    Thanks,

    Genevieve 

  • Lisa Vercellone
    Lisa Vercellone ✭✭✭✭

    Is there an update on this issue? I am having the same exact same problem. I will try the work around but was hoping there was a fix in the works.


    Thanks, Lisa

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Lisa Vercellone

    Can you copy/paste in your exact formula? We could potentially build in the VALUE function right into the formula.

  • Stephanie Graham
    Stephanie Graham ✭✭✭✭

    @Genevieve P. Was there an update to using duplicate checker with leading zero's? Our employee numbers must have 8 digits and have leading zero's in some cases.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Stephanie Graham

    Can you clarify what the problem is? Is it that in one sheet there are leading 0's and in another sheet the same number is missing the 0? Or do both sheets have a 0?

    If you're seeing odd results, I would suggest using an @cell = in front of your criteria, like so:

    =IF(COUNTIF([employee #]:[employee #], @cell = [employee #]@row) > 1, 1, 0)

  • Stephanie Graham
    Stephanie Graham ✭✭✭✭
    edited 03/15/24

    Hi @Genevieve P.

    This still gave me an error (formula syntax). I am using the formula below.

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

    Our personnel numbers are 8 digits long (Ex, 12345678 or 00012345).

    The formula works and checks a box for duplicates found IF they do not start with a leading 0. It is finding numbers such as 12345678 or blank spaces and marking those lines as duplicates. The cells with leading 0's have and apostrophe at the beginning to keep the leading 0's therefore the formula is not triggering. It's also not recognizing that they are text and finding them as duplicate text.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Stephanie Graham

    Have you tried adding in the @cell suggestion?

    =IF(COUNTIF([Personnel Number]:[Personnel Number], @cell = [Personnel Number]@row) > 1, 1, 0)

    I've found that when there are leading 0's it's best to use the @cell statement.

  • Stephanie Graham
    Stephanie Graham ✭✭✭✭

    I must have had a typo in the last one you suggested. Somehow I copied this one correctly! Thank you, I tested it with a couple of numbers and this worked. I really appreciate it. This saves hours of work!

  • Genevieve P.
    Genevieve P. Employee Admin

    Amazing! Glad I could help 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!