Find location and number of consecutive occurrences of 1 repeated ≥25 times in a column

Hi,

So the column A entries are always consecutive integers beginning at 1?

If so, and you put your threshold for consecutive 1s (e.g. 25) in C1, then:

In D2:

=IFERROR(AGGREGATE(15,6,A$2:A$41/(MID(CONCAT(0,B$2:B$41),A$2:A$41,C$1+1)=0&REPT(1,C$1)),ROWS(A$2:A2)),"")

In E2:

=IF(D2="","",IFERROR(MATCH(0,B$41:INDEX(B$2:B$41,D2),0)-1,C$1))

Copy both down until you start to get blanks for the results.

Note that if your actual data comprises more than 32,767 rows then this set-up will error. If that's the case I can give you an alternative solution.

Regards

Hi XOR LX, thank you so much! This mostly worked, but there is just one problem with the second formula (in E2). I forgot to mention in that in Column B, there are also dashes (-). I only want rows containing 1s. However, if I have 25 1s followed by 4 dashes, the formula will give me 29. How can I fix this?

(I've attached a new spreadsheet with the a very long column of data containing 0s, dashes, and 1s in column B to show what I mean).

Hi,

Does this adjustment fix it for you?

In E3:

=IF(D3="","",IFERROR(MIN(MATCH({0,"-"},B$7500:INDEX(B$2:B$7500,D3),0))-1,C$1))

By the way, I noticed that you'd committed the column D formulas as array formulas, i.e. with CTRL+SHIFT+ENTER - they don't need to be, in fact.

Regards

Ah yes, of course. Apologies. Use:

=IF(D3="","",IFERROR(AGGREGATE(15,6,MATCH({0,"-"},B$7500:INDEX(B$2:B$7500,D3),0),1)-1,C$1))

instead.

Regards

Thank you so much, it works! You've really made my day. This is incredibly helpful for me! The formulas will save me so much time.
I hope you have a great week.
Montana