Flagging "first occurrence" & "duplicate" as a column formula to identify unique values.

Hello! I have a sheet that I am treating like a database for risk files for my company. Every risk file + product combination has its own row. Certain products share the same "risk file", but every risk file + product combination gets its own row so I am able to filter by product and see all relevant files for a given product. However, I want to maintain counts of UNIQUE risk files, so I want to flag every file number's first occurrence.

The formula below works great, however, the syntax does not allow it to be converted to a column formula.

=IF(COUNTIF([File Number]$1:[File Number]@row, [File Number]@row) = 1, "First Occurrence", "Duplicate")

I have tried some alternate syntax, including the syntax below. This can be converted to a column formula, but it flags the first occurrence as a "duplicate" if that file number appears again later in the column.

COUNTIF([File Number]:[File Number], [File Number]@row) = 1

Thanks

Dave

Find more posts tagged with

Accepted answers

All comments

Michelle Choate 2

You are super close. Switch to >1 and it will check the second occurrence.

Dave Werwinski

Hi @Michelle Choate 2 , thanks for your help! If I am following correctly, the syntax you mean is this?

=IF(COUNTIF([File Number]:[File Number], [File Number]@row) = 1, "First Occurrence", IF(COUNTIF([File Number]:[File Number], [File Number]@row) > 1, "Duplicate", ""))

I tried this, and although it works as a column formula, it still incorrectly flags the first occurrence as a "duplicate" for any risk file number that appears again in the column

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.