Formula that can be applied as a Column Formula that marks unique values/entries out in a list
Hi All - I have a sheet that has a list of URLs in one column - lets call that column [HIRE URL]
Some of these URLs are duplicated and I am looking to use a Helper column to mark which ones are unique entries.
I found this formula in another forum post which I adapted to my needs;
=IF(COUNTIFS([HIRE URL]$1:[HIRE URL]@row, [HIRE URL]@row) = 1, 1)
However - this formula cannot be converted to a Column Formula, so it will require ongoing manual effort to keep adjusting it.
Is there a work around available so that I can convert this to a Column Formula? If I add in a row number column and use the MIN function perhaps? Any guidance on this?
Thanks in advance!
Best Answer
-
You would need to insert an auto-number type column (called "Auto") with no special formatting required. Then you would insert a text/number type column (called "Row") and enter this column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Then you would adjust your COUNTIFS to look at the entire [HIRE URL] column and include another range/criteria set where the Row column is less than or equal to Row@row.
Answers
-
You would need to insert an auto-number type column (called "Auto") with no special formatting required. Then you would insert a text/number type column (called "Row") and enter this column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Then you would adjust your COUNTIFS to look at the entire [HIRE URL] column and include another range/criteria set where the Row column is less than or equal to Row@row.
-
Paul - thank you - that worked!
=IF(COUNTIFS([HIRE URL]:[HIRE URL], [HIRE URL]@row, Row:Row, <=Row@row) = 1, 1)
I have a slightly different challenge now that I am maxing the amount of cells referenced in the sheet - so won't be able to fully populate the amount of rows I am likely to need, but I can solve that separately, thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!