Identify which row or cell, the start of value 0 be the most appear in sequence.

[ibmy]

Hi,

My interest value for the data is "0", zero. The value 0 must be the most appear in sequence. (sorry for my bad english)

Example:

________| Column A
Row 1 : 2
Row 2 : 0 <-
Row 3 : 3
Row 4 : 0 <-
Row 5 : 0 <-
Row 6 : 0 <-
Row 7 : 0 <-
Row 8 : 4
Row 9 : 0 <-
Row 10 : 1
Row 11 : 0 <-
Row 12 : 0 <-
Row 13 : 0 <-
Row 14 : 0 <-
Row 15 : 0 <-
Row 16 : 0 <-
Row 17 : 0 <-
Row 18 : 7
Row 19 : 6
Row 20 : 6
.
.
.

At row 2 and row 9, 0 only appear 1 time and have no sequence.
At row 4 to row 7, 0 appear 4 times.
While at row 11 to 17, 0 appear 7 times.
So row 11 is the start for value 0 to be appear the most in sequence.

Help me on the data 250k row on each column, which row or cell the start for value 0 to be appear the most in sequence.
Thanks.

 

[jasonb75]

See post 3 in this thread

Count consecutive rows with same value

I have a column with 12682 rows, with values between 1 and 31. I want to know how many times each number shows up consecutively. Here is the first 18 cells in the column: 6 12 14 14 17 17 13 13 16 9 7 7 5 6 6 1 1 1 So for this I want to know that there...

www.mrexcel.com

 

[hajiali]

I was able to accomplish this whit few helper cells. first i would start the data in row 2 due to the formula in column C.
Helper Column B simply puts a 1 were ever a 0 appears in column A
Helper Column C is an array formula whey sums the value in B till the next blank cell in B. Hint You have to press Ctrl+Shift+enter to make the the array formula
Cell D1 is the final results.

you can hide both helper Column B & C.

Book1
	A	B	C	D
1	START OF MOST "0" IN SEQUENCE			12
2	2
3	0	1	1
4	3
5	0	1	4
6	0	1
7	0	1
8	0	1
9	4
10	0	1	1
11	1
12	0	1	6
13	0	1
14	0	1
15	0	1
16	0	1
17	0	1
18	7
19	6
20	6
21	6
Sheet1

 

[jasonb75]

I was looking at the formula in the link that I posted and realised that it doesn't work as would be expected, This one works based on your example, but may be a bit slow to calculate with 250k rows. Must be array confirmed with Ctrl Shift Enter.

=SUM(INDEX(FREQUENCY(ROW(A1:A20),IF(A1:A20<>0,ROW(A1:A20))),N(IF({1},ROW(INDEX(A:A,1):INDEX(A:A,MATCH(MAX(FREQUENCY(ROW(A1:A20),IF(A1:A20<>0,ROW(A1:A20)))),FREQUENCY(ROW(A1:A20),IF(A1:A20<>0,ROW(A1:A20))),0)-1))))),1)

I'm going to take a look and see if I can improve it while you test it with smaller data sets to confirm that the results are as expected.

 

[jasonb75]

I found that there was an error if the max consecutive zero's starts in row 1, fixing that caused an error if there were no zero's anywhere in the column. This revised formula corrects both problems, although it is far from efficient.

=IF(COUNTIF(A:A,0)=0,"N/A",IFERROR(SUM(INDEX(FREQUENCY(ROW(A1:A20),IF(A1:A20<>0,ROW(A1:A20))),N(IF({1},ROW(INDEX(A:A,1):INDEX(A:A,MATCH(MAX(FREQUENCY(ROW(A1:A20),IF(A1:A20<>0,ROW(A1:A20)))),FREQUENCY(ROW(A1:A20),IF(A1:A20<>0,ROW(A1:A20))),0)-1))))),1),1))

I think that a multiple column approach similar to @hajiali's suggestion would be better than a single formula.

 

[jolivanes]

It assumes that your data starts at A1 and that Column B is empty and free to use.

Sub AAAAA()
Dim lr As Long, highNr As Long, highNrRow As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
    With Range("A2:A" & lr).Offset(, 1)
        .Formula = "=IF(RC[-1]<>0,"""",IF(AND(RC[-1]=0,R[-1]C[-1]=0),R[-1]C+1, 1))"
        .Value = .Value
    End With
    highNr = Application.Max(Columns(2))
        Set highNrRow = Columns(2).Find(highNr)
    MsgBox "The highest number starts at row " & highNrRow.Row - (highNr - 1)
End Sub

 

[ibmy]

Thanks @jasonb75 @hajiali @jolivanes

All works great

Really appreaciate all your help.