Count cycle period

[Kishan]

Hi,

I need to count cycle of 1X2 period when each time it is completed.

Data in Cells C6:C66 Result in cells E6:E14 colours are just shown to show example clearly.

Example data

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

Thank you in advance

Kishan

 

[oldbrewer]

View your post something is missing

 

[oldbrewer]

without the colors how do we know how many rows are to be considered each time

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG12Sep39
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, C [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Fd1 [COLOR="Navy"]As[/COLOR] Boolean, Fd2 [COLOR="Navy"]As[/COLOR] Boolean, Fd3 [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C6"), Range("C" & Rows.Count).End(xlUp))
Rw = 5
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    n = n + 1
    [COLOR="Navy"]If[/COLOR] Not Fd1 And Dn.Value = "X" [COLOR="Navy"]Then[/COLOR] Fd1 = True: C = C + 1
    [COLOR="Navy"]If[/COLOR] Not Fd2 And Dn.Value = 1 [COLOR="Navy"]Then[/COLOR] Fd2 = True: C = C + 1
    [COLOR="Navy"]If[/COLOR] Not Fd3 And Dn.Value = 2 [COLOR="Navy"]Then[/COLOR] Fd3 = True: C = C + 1
    [COLOR="Navy"]If[/COLOR] C = 3 [COLOR="Navy"]Then[/COLOR]
        Fd1 = False: Fd2 = False: Fd3 = False
        Rw = Rw + 1
        Cells(Rw, "E") = n
        n = 0: C = 0
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kishan]

without the colors how do we know how many rows are to be considered each time

Hi oldbrewer, I do not need to count the colour cells I have tried to show by colour in the example that when the each time Cycle of 1, X & 2 has completed.

For example...

1st time 1, X & 2 cycle is completed in cells C6:C10 and the result 5 in cell E6 so I have highlighted the cells C6:C10 in cyan and the result cell E6 in colour cyan.

2nd 1, X & 2 cycle is completed in cells C11:C22 and the result 5 in cell E7 so I have highlighted the cells C11:C22 in magenta and the result cell E7 in colour magenta.

And so on...

Hope this help

And so on...

Regards,
Kishan

 

[Kishan]

Try this:-

[COLOR=navy]Sub[/COLOR] MG12Sep39
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, C [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Fd1 [COLOR=navy]As[/COLOR] Boolean, Fd2 [COLOR=navy]As[/COLOR] Boolean, Fd3 [COLOR=navy]As[/COLOR] Boolean
[COLOR=navy]Set[/COLOR] Rng = Range(Range("C6"), Range("C" & Rows.Count).End(xlUp))
Rw = 5
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    n = n + 1
    [COLOR=navy]If[/COLOR] Not Fd1 And Dn.Value = "X" [COLOR=navy]Then[/COLOR] Fd1 = True: C = C + 1
    [COLOR=navy]If[/COLOR] Not Fd2 And Dn.Value = 1 [COLOR=navy]Then[/COLOR] Fd2 = True: C = C + 1
    [COLOR=navy]If[/COLOR] Not Fd3 And Dn.Value = 2 [COLOR=navy]Then[/COLOR] Fd3 = True: C = C + 1
    [COLOR=navy]If[/COLOR] C = 3 [COLOR=navy]Then[/COLOR]
        Fd1 = False: Fd2 = False: Fd3 = False
        Rw = Rw + 1
        Cells(Rw, "E") = n
        n = 0: C = 0
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank you MickG, your code is giving the answers correct even mine data are not correct in the opening post#1

It should be the result as your code is generating as shown below which is 100% Perfect!!

Book1
	D	E	F
1
2
3
4
5		Count Cycle
6		5
7		12
8		11
9		4
10		5
11		5
12		7
13		3
14		4
15		5
16
17
Sheet1

Thank you so much for your help

Kind Regards,
Kishan

 

[MickG]

You're welcome

 

[Eric W]

Just as an interesting side note, you can do this with formulas too:

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula E6 =IFERROR(MAX(MATCH("X",OFFSET($C$6:$C$66,SUM(E$5:E5),0),0),MATCH(1,OFFSET($C$6:$C$66,SUM(E$5:E5),0),0),MATCH(2,OFFSET($C$6:$C$66,SUM(E$5:E5),0),0)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Kishan]

Just as an interesting side note, you can do this with formulas too:

Hi Eric, the formula you suggested it is not working with my version I tried to change but no results.

Thank you for your help

Kind Regards,
Kishan

 

[Eric W]

I think you have Excel 2000? That doesn't have the IFERROR function. You can just remove it like this:

=MAX(MATCH("X",OFFSET($C$6:$C$66,SUM(E$5:E5),0),0),MATCH(1,OFFSET($C$6:$C$66,SUM(E$5:E5),0),0),MATCH(2,OFFSET($C$6:$C$66,SUM(E$5:E5),0),0))

I believe the rest of the functions work in 2000. You'll just get an error at the end of the list instead of an empty cell.