Issue with leading zeroes when Identifying duplicates

cnikkih

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)

Genevieve P.

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

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.

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

@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.

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

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.

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

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.

Amazing! Glad I could help 🙂