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?

Thanks in advance!

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    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.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    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.

  • .Leon.
    .Leon. ✭✭
    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!