Issue with leading zeroes when Identifying duplicates
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
-
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,
GenevieveNeed more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
Can you copy/paste in your exact formula? We could potentially build in the VALUE function right into the formula.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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.
-
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)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
-
Amazing! Glad I could help 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!