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 autonumber 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 autonumber 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
Check out the Formula Handbook template!