Counting Unique and First Values Using MIN / COLLECT

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. 🤷🏻‍♀️

Paul Newcome

You could try something like this...

=IF(COUNTIFS([Lookup Index]$1:[Lookup Index]@row, [Lookup Index]@row) = 1, 1)

Paul Newcome

The reason the MIN function still isn't working is because keeping the leading zeros is converting the data into text. The [Row ID] column is still not numerical.

Genevieve P.

Hi @Danielle Arteaga

The MIN function can only be used with numerical data or dates. Currently the way your auto-number column is set up has text in front of it, as well as a number. This means the MIN will return 0, since there are 0 numbers in that column.

You could potentially add in a different, helper/hidden System column (like a created date column) to then use in the MIN formula. It would be the exact same formula, just referencing a different column to check and find the MIN.

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

Note that in my example I also added an = sign after the COUNT range... as I presume you also want to check boxes for rows that only have one unique entry (is that correct?).

Let me know if this works for you!

Cheers,

Genevieve

Danielle Arteaga

Hi, Genevieve - thanks so much for the suggestion. I modified my Row ID column so that it is only numeric (no alpha prefix), but still nothing.

I also tried adding an auto-create column as you suggested, and I got the opposite result - EVERY box is checked.

Similarly, I tried alternating the formula to reference [Repeat Product Family] (which converts [Product Family] to lower case), but no change.

Paul Newcome

You could try something like this...

=IF(COUNTIFS([Lookup Index]$1:[Lookup Index]@row, [Lookup Index]@row) = 1, 1)

Paul Newcome

The reason the MIN function still isn't working is because keeping the leading zeros is converting the data into text. The [Row ID] column is still not numerical.

Danielle Arteaga

https://community.smartsheet.com/discussion/comment/265338#Comment_265338

Perfect. Of course it was this easy. ;-) Thank you.

Genevieve P.

Thanks, Paul!! Great answer.

Paul Newcome

@Danielle Arteaga Happy to help. 👍️

@Genevieve P Thanks! Sometimes it just takes an extra set of eyes.

mrsci3ntist

Hi @Paul Newcome,

Is there a way to modify this formula into a column formula ?

=IF(COUNTIFS([Lookup Index]$1:[Lookup Index]@row, [Lookup Index]@row) = 1, 1)

Looking to identify the first instance of a value in the column and if true, return value 1.. in my case a second column will be required to sum the above column results (although only from first row to current row) → is there a column formula to achieve this as well?

Thanks!

Paul Newcome

@mrsci3ntist Yes, but… It looks like this would output the same result as counting distinct values within the column once you sum the results form the IF. Try a COUNT/DISTINCT combo to see if that gets you the same end result:

=COUNT(DISTINCT([Lookup Index]:[Lookup Index]))

If you need to filter the data such as only those that fall within certain date ranges or for a specific person, dept., etc., you would use a COLLECT function inside of the DISTINCT.

mrsci3ntist

@Paul Newcome, thank you, I used your recommendation for COUNT(DISTINCT()) in another instance and it did as expected!

In this particular case I found a workaround after asking this question to the community, you'll see I've referenced another discussion as well:

https://community.smartsheet.com/discussion/comment/448534#Comment_448534