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

.Leon.

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!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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.

All comments

Paul Newcome

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.

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.