Finding Duplicate Formula not working
I'm trying to find duplicates in [Invoice Number] column. I added a column "Duplicates" and wrote the formula:
=COUNTIF([Invoice Number]:[Invoice Number], [Invoice Number]@row)
Then I could conditionally format any count over 1 and thus finding the duplicates.
Its working for the most part, but I have identified one duplicate in the [Invoice Number] column that's not counting, its returning the value of 0. However, If I retype the duplicate invoice numbers that are duplicated, the formula works. ??? When I do retype, the cell value shifts to the other side of the cell as if the field format changed when I retyped the number. This is an imported sheet, not sure if a "cell format" in excel is overwriting the column format of Text/Number in Smartsheet?
Another issue is some cells where the [Invoice Number] isn't duplicated, its returning the value of "0" rather than "1".
All other duplicates are working. Counting if duplicated, returning value of 1 when not.
Answers
-
It sounds like you have a mix of text and number strings in your [Invoice Number] column. Converting them all into text strings in a hidden helper column and then referencing this helper column in your duplicates formula should do the trick.
=[Invoice Number]@row + ""
[column name] plus quote quote
-
Please disregard - I hit the post comment button by accident and now I don't see a way to delete, just edit.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!