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.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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

DKazatsky2

Please disregard - I hit the post comment button by accident and now I don't see a way to delete, just edit.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.