Formula to mark duplicates with a Status of 2 or 3

Hi Everyone,

Hoping I can explain this well so that someone out there can help me with this.

I have built a solution that is quite complex and moves projects around to 3 different sheets.

The intake sheet generates a project ID and then when the project gets to a certain stage, it copies to my Active project sheet, but then I also have it copy once or twice to a Project Queue sheet because each project can span 2 or 3 years. With this approach, I get 2 or 3 rows for each project without having to manually update and they have have the same Project ID. Love that. The piece I'm having trouble with, is that if a project has 2 additional rows on the queue sheet, I need to be able to mark one as status 2 (year 2) and one as status 3 (year 3). I'm currently using this formula:

=IF(COUNTIF(ID$1:ID@row, ID@row) = 1, 2, IF(COUNTIF(ID$1:ID@row, ID@row) = 2, 3, ""))

The problem with this is that I cannot make it a column formula, and I dont want to run into the issue of the formula not dragging down as it should, as I will not be using this sheet myself, and if it breaks, I dont think my users would notice.

I tried to use this: =IF(COUNTIF(ID:ID, ID@row) = 1, 2, IF(COUNTIF(ID:ID, ID@row) = 2, 3, ""))

However it's not giving me the correct results.

I need the first instance of a project to be a status of 2, the 2nd to be a 3 and there shouldnt be an additional, but if there were, blank so they dont get counted.

PLEASE HELP!!

Find more posts tagged with

Formulas

copy

duplicates

Accepted answers

All comments

jessica.smith

Hi @Tina Ciak,

I think I understand what you need, but apologies if I've misunderstood.

You need a mechanism in your sheet to order the duplicate projects. This could either be something like a created date or row order if row order will reliably correspond to status/year.

If you were to base on a created date, your formula would look something like:

=COUNTIFS(ProjectID:ProjectID, ProjectID@row, Created:Created, <Created@row) + 1

If you want to base on row order, then you need to add some columns to identify the row. I usually do Auto, which is an system auto number column and RowID which includes the formula =Match(auto@row,auto:auto,0)

If you go this route, your status column formula will be

=COUNTIFS(ProjectID:ProjectID, ProjectID@row, RowID:RowID, <RowID@row) + 1

jessica.smith

I just noticed you want to return a blank value if the count of duplicates is greater than 3… to do that, you would add an IF statement to your formula that looks something like:

=IF(COUNTIFS(ProjectID:ProjectID, ProjectID@row, Created:Created, <Created@row) > 2, "", COUNTIFS(ProjectID:ProjectID, ProjectID@row, Created:Created, <Created@row) + 1)

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.