MATCH alternative

[mirusev]

That MATCH() does what I need - finding series of ones, but when rows exceed 300 000 it stuck.
Do I need to split the raw data on 300k rows?
What is the better alternative to get these results (if any)? In fact I need it for few millions of rows.

Thanks!

check-500k.xlsx
	C	D
1	1	2
2	1	1
3	0	0
4	0	0
5	1	1
6	0	0
7	0	0
8	1	2
9	1	1
10	0	0
Sheet1

Cell Formulas
Range	Formula
C1:C10	C1	=IF(B1>=$F$1,0,1)
D1:D10	D1	=MATCH(0,C1:C260000,0)-1

 

[Peter_SSs]

In fact I need it for few millions of rows.

Not sure how you are going to deal with that since Excel only has 1,048,576 rows.

Still up to about that you could try this

20 07 26.xlsm
	C	D
1	1	2
2	1	1
3	0	0
4	0	0
5	1	1
6	0	0
7	0	0
8	1	2
9	1	1
10	0	0
Match

Cell Formulas
Range	Formula
D1:D10	D1	=MATCH(0,C1:C$1048576,0)-1

 

[mirusev]

It is OK to be up to 1,048,576 rows, I will split it on 3. But it can not pass more than 300 max 400 000 rows. Excel just stops and/or crashes
It is on i7 with 16 GB (still has free memory when run), installed the 64 bits version, but the problem remains, so may be another approach is the solution, not that based on MATCH()

 

[Peter_SSs]

You could try this macro instead

Sub Count_Ones()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, fr As Long
  
  a = Range("C1", Range("C" & Rows.Count).End(xlUp).Offset(1)).Value
  ReDim b(1 To UBound(a) - 1, 1 To 1) As Long
  Do
    i = i + 1
    If a(i, 1) = 1 Then
      fr = i
      Do
        k = k + 1
      Loop Until a(i + k, 1) = 0
      For j = 1 To k
        b(fr + j - 1, 1) = k - j + 1
      Next j
      i = i + k - 1
      k = 0
    End If
  Loop Until i >= UBound(a)
  Range("D1").Resize(UBound(b)).Value = b
End Sub

 

[Dermot]

There's a way to do it using 2 columns and simple formulae. I tested it with a million rows and it calculated quickly.

Book1
	A	B	C
5	0	0	0
6	1	1	1
7	0	0	0
8	0	0	0
9	1	5	5
10	1	4	0
11	1	3	0
12	1	2	0
13	1	1	0
14	0	0	0
15	1	2	2
16	1	1	0
Sheet2

Cell Formulas
Range	Formula
B5:B16	B5	=IF(A5=1,B6+1,0)
C5:C16	C5	=IF(B4=0,B5,0)

 

[mirusev]

You could try this macro instead

Sub Count_Ones()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, fr As Long
 
  a = Range("C1", Range("C" & Rows.Count).End(xlUp).Offset(1)).Value
  ReDim b(1 To UBound(a) - 1, 1 To 1) As Long
  Do
    i = i + 1
    If a(i, 1) = 1 Then
      fr = i
      Do
        k = k + 1
      Loop Until a(i + k, 1) = 0
      For j = 1 To k
        b(fr + j - 1, 1) = k - j + 1
      Next j
      i = i + k - 1
      k = 0
    End If
  Loop Until i >= UBound(a)
  Range("D1").Resize(UBound(b)).Value = b
End Sub

Totally amazing!!! I love you!

 

[mirusev]

There's a way to do it using 2 columns and simple formulae. I tested it with a million rows and it calculated quickly.

Book1
	A	B	C
5	0	0	0
6	1	1	1
7	0	0	0
8	0	0	0
9	1	5	5
10	1	4	0
11	1	3	0
12	1	2	0
13	1	1	0
14	0	0	0
15	1	2	2
16	1	1	0
Sheet2

Cell Formulas
Range	Formula
B5:B16	B5	=IF(A5=1,B6+1,0)
C5:C16	C5	=IF(B4=0,B5,0)

Also great alternative! Highly appreciated! Thank you