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

Dave Werwinski

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

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