Checkbox for Unique Item Name

Alexander Becker

Hello!

I'm trying to create a formula for checking a box for only the first instance of a unique value in the column "ITEM NAME". IE it sees the first instance of "Event Case 1" and checks the box for only that row, and the remaining instances don't get checked.

I tried using the AI tool but the formula it creates can't be used as a column formula which means we are always having to drag the formula down. I believe it's because it has a row reference in it.

I've tried a couple different formats and formulas but can't figure it out. Any help would be greatly appreciated!

Larry

Oh I see, my mistake. I adjusted your original formula & missed the "first instance" issue.
You will need to add an Auto # column like I have done in the screenshot.I generally just title Row ID & start at 1 for simplicity.
The new formula looks a bit more complex:

=IF(COUNT(COLLECT([Row ID]:[Row ID], [ITEM NAME]:[ITEM NAME], [ITEM NAME]@row, [Row ID]:[Row ID], [Row ID]@row <= [Row ID]@row)) = 1, 1, 0)

This is simply just finding the corresponding Row IDs w/ the matching event name & making sure it is the first (smallest Row ID value)

Larry

You just need to adjust the Item Name Reference slightly so it is looking at the entire column & not a specifc row as you mentioned.

This formula should work:

=IF(COUNTIF([ITEM NAME]:[ITEM NAME], [ITEM NAME]@row) = 1, 1, 0)

Alexander Becker

Hi @Larry - Yes I did try this version already, but I did just try it again.

Not sure what's the issue but it doesn't seem to work. You can see in the screenshot once I applied it to the column it doesn't check the box for "Event Case 1" and "Roadcase-4" for example. I did check that they didn't appear down the sheet elsewhere. There were many more examples down the sheet where it wasn't working properly as well.

Larry

Oh I see, my mistake. I adjusted your original formula & missed the "first instance" issue.
You will need to add an Auto # column like I have done in the screenshot.I generally just title Row ID & start at 1 for simplicity.
The new formula looks a bit more complex:

=IF(COUNT(COLLECT([Row ID]:[Row ID], [ITEM NAME]:[ITEM NAME], [ITEM NAME]@row, [Row ID]:[Row ID], [Row ID]@row <= [Row ID]@row)) = 1, 1, 0)

This is simply just finding the corresponding Row IDs w/ the matching event name & making sure it is the first (smallest Row ID value)

Alexander Becker

Amazing! We have a Row ID column already so this was an easy and quick fix with your solution. Thanks so much @Larry !!