Issue with leading zeroes when Identifying duplicates

cnikkihcnikkih ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
10/21/19 Edited 12/09/19

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)

Comments

  • Genevieve P.Genevieve P. 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 

  • 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

  • Hi @Lisa Vercellone

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

Sign In or Register to comment.