Count cycle 1&0 or 0&1 visa versa.

[motilulla]

Using Excel 2010
Hello,

In the 2 columns C & D there are filled numbers 0’s & 1’s, I want column C count result in column F and column D count result in column G.

For example 0&1 or 1&0 is one cycle…1st cycle start in C6 with 0 and end with 1 in C10. Cycle count result in F6:F10 =1, 2, 3, 4, 5….2nd cycle start in C11 with 0 and end with 1 in C13. Cycle count result in F11:F13 =1, 2, 3….and so on…

Same logic will be applied for column D and results in column G.
Note: columns are coloured just to explain example clearer.

Please advise any formula or VBA solution.

MrExcel Question.xlsm
	A	B	C	D	E	F	G	H
1
2
3
4
5			n1	n2		n1	n2
6			0	1		1	1
7			0	1		2	2
8			0	1		3	3
9			0	1		4	4
10			1	1		5	5
11			0	1		1	6
12			0	0		2	7
13			1	1		3	1
14			1	1		1	2
15			1	1		2	3
16			0	0		3	4
17			1	1		1	1
18			1	1		2	2
19			0	1		3	3
20			1	0		1	4
21			1	0		2	1
22			0	1		3	2
23			1	1		1	1
24			0	1		2	2
25			1	1		1	3
26			0	1		2	4
27			1	0		1	5
28			1	0		2	1
29			1	1		3	2
30			1	0		4	1
31			1	1		5	2
32			1	0		6	1
33			0	0		7	2
34			1	1		1	3
35			0	0		2	1
36			1	0		1	2
37			0	1		2	3
38			0	1		1	1
39			0	0		2	2
40			1	0		3	1
41			0	1		1	2
42			0	1		2	1
43			0	1		3	2
44			0	1		4	3
45			1	1		5	4
46			1	1		1	5
47			0	0		2	6
48			1	1		1	1
49			0	1		2	2
50			0	1		1	3
51			0	0		2	4
52			0	1		3	1
53			0	1		4	2
54			0	0		5	3
55			0	0		6	1
56			1	0		7	2
57			0	1		1	3
58			1	1		2	4
59			1	1		1	5
60			0	1		2	6
61			1	0		1	7
62			1	0		2	1
63			1	0		3	2
64
Count Cycle

Regards,
Moti

 

[Cubist]

Try this UDF. Paste this into a VBA module.

Function ZeroOneCycle(rng As Range) As Variant
    Dim a As Range, b()
    Dim lastRow As Long
    Dim i As Long, k As Long
    Dim startCycle As Long
  
    lastRow = rng.Rows.Count
    startCycle = rng.Cells(1, 1).Value
    ReDim b(1 To lastRow, 1 To 1)
  
    k = 1
    b(1, 1) = k
  
    For i = 2 To lastRow
        If rng.Cells(i, 1).Value = startCycle Then
            k = k + 1
            b(i, 1) = k
        Else
            b(i, 1) = k + 1
            If i <= lastRow Then startCycle = rng.Cells(i + 1, 1).Value
            k = 0
        End If
    Next i

    CountCycles = b
End Function

In the output cell, enter

=ZeroOneCycle(C3:C63)

 

[Cubist]

Function ZeroOneCycle(rng As Range) As Variant
    Dim a As Range, b()
    Dim lastRow As Long
    Dim i As Long
    Dim startCycle As Long
    
    lastRow = rng.Rows.Count
    startCycle = rng.Cells(1, 1).Value
    ReDim b(1 To lastRow, 1 To 1)
    
    k = 1
    b(1, 1) = k
    
    For i = 2 To lastRow
        If rng.Cells(i, 1).Value = startCycle Then
            k = k + 1
            b(i, 1) = k
        Else
            b(i, 1) = k + 1
            If i <= lastRow Then startCycle = rng.Cells(i + 1, 1).Value
            k = 0
        End If
    Next i

    ZeroOneCycle = b
End Function

 

[motilulla]

Cubist, “Function ZeroOneCycle” worked perfect as per my request

I admire your help and thank you for the time you spent. Good Luck!

Kind Regards,
Moti