Counting Unique and First Values Using MIN / COLLECT

Danielle Arteaga
Danielle Arteaga ✭✭✭✭✭✭

@Paul Newcome - I tried using a solution you recommended on another thread for counting the truly unique and first-instance-of-duplicate values in a column, and I've hit a wall - could use your help (or anyone else who is way smarter at formulas than I am ...)

[Row ID] = Auto-number

[Lookup Index] = Join of [Product Family] + [BE2] + [Sub BE] to create unique string

[Product Family] = alphanumeric string. This is imported through Data Uploader from another source. Cannot be edited.

COLUMNS YOU CAN"T SEE in screenshot ...

[Count] = Count of how many times the same alphanumeric string appears in the [Product Family] column

[First Entry] = checkbox column where the formula will ultimately go to identify unique + first-instance values in [Product Family] column.

I tried this formula:

=IF([Row ID]@row = MIN(COLLECT([Row ID]:[Row ID], Count:Count, >1, [Product Family]:[Product Family], [Product Family]@row)), 1)

The formula "works" (meaning, no errors), but it doesn't check the box for ANYTHING. Cannot figure out why. I thought maybe Smartsheet did not like the all-caps in the Product Family name, so I tried converting to lowercase, but still nothing. 🤷🏻‍♀️

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!