Formula to mark duplicates with a Status of 2 or 3

Tina Ciak

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!!

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)