how to sequence a set with reseting condition only if previous state got terminated-CodePudding

given the specific set of columns, I'm looking for a way to turn red cells into blank cells either within the COUNTIF process or afterward.

formula I use to get (partially) correct answer is:

=ARRAYFORMULA(IF(A2:A="",,IF(NOT(REGEXMATCH(TO_TEXT(B2:B), "\-")), 
 COUNTIFS(A2:A&D2:D, A2:A&D2:D, ROW(A2:A), "<="&ROW(A2:A)), )))

here is a copy of my sheet: - CC -

consider yellow cells as RESET/TERMINATION for ID
after each reset, ID gets upgraded (E column)
E column is just a helper for better visualization - it's not part of the dataset
A column is unsorted and it may be even ungrouped

CodePudding user response：

When I make a copy of your sheet (the only option provided), the negative numbers in Col B are actually all text. So I didn't backtrack into the best way of doing this from the original data in Col A. Instead, I just worked with Cols A:D as you currently have them (though I suspect that you don't need the helpers) and with your original "close" formula as you wrote it.

That said, this should work to deliver your desired result as shown:

=ArrayFormula({"DESIRED OUTPUT";IF(A2:A="",,IF(VLOOKUP(A2:A&"~"&COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))-1,{SPLIT(UNIQUE(A2:A)&"~0|0","|");A2:A&"~"&COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)),C2:C},2,FALSE)<>0,,IF(NOT(REGEXMATCH(TO_TEXT(B2:B), "\-")), COUNTIFS(A2:A&D2:D, A2:A&D2:D, ROW(A2:A), "<="&ROW(A2:A)), )))})

My contribution is this part:

IF(VLOOKUP(A2:A&"~"&COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))-1,{SPLIT(UNIQUE(A2:A)&"~0|0","|");A2:A&"~"&COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)),C2:C},2,FALSE)<>0,, [most of your previous formula])

In plain English, this looks up the last occurrence of each ID. Only if that last occurrence had a 0 value in Col C will the results of your main formula be shown.

I stacked SPLIT(UNIQUE(A2:A)&"~0|0","|") on top of the actual ID~Row, Col C values so that the first occurrence of any ID will still find a 0 and will not result in an error. (Adding IFERROR would have unnecessarily lengthened the formula.)

NOTE 1: I assumed here that you original "close" formula works as you expect. I did not test it under close scrutiny. I just plugged it into my extended formula that determines where to place results.

NOTE 2: Normally, I don't get into complex formulas on these forums, for the sheer sake of time and the fact that I stay very busy. But you help out a ton on this forum, so I was happy to invest back into your own rare inquiry here.