# Formula that can be applied as a Column Formula that marks unique values/entries out in a list

Options
✭✭

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?

Tags:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!