Counting Unique and First Values Using MIN / COLLECT
@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
-
You could try something like this...
=IF(COUNTIFS([Lookup Index]$1:[Lookup Index]@row, [Lookup Index]@row) = 1, 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
-
You could try something like this...
=IF(COUNTIFS([Lookup Index]$1:[Lookup Index]@row, [Lookup Index]@row) = 1, 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Perfect. Of course it was this easy. ;-) Thank you.
-
Thanks, Paul!! Great answer.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Danielle Arteaga Happy to help. 👍️
@Genevieve P Thanks! Sometimes it just takes an extra set of eyes.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!