Check cycle of character 1X2

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

Data cells C6:C78, highlighted every 7th row with black background-white fonts, my query is I need to check cycle for 3 characters 1X2 when it is completed.</SPAN></SPAN>

For the example...</SPAN></SPAN>

C6=X, C13=1, C20=2, so far cycle of 1X2 is completed starting from C6 within 14 rows, so far RESULT in row D20=14 </SPAN></SPAN>

C27=1, C34=2, C41=1(repeated), C48=X, so far next cycle of 1X2 is completed starting from C20 within 28 rows, so far RESULT in row D48=28</SPAN></SPAN>

And so on...</SPAN></SPAN>

Example sample data</SPAN></SPAN>

Book1
	A	B	C	D	E
1
2
3
4
5			P1
6			X
7			1
8			X
9			2
10			1
11			X
12			1
13			1
14			X
15			1
16			1
17			1
18			1
19			1
20			2	14
21			1
22			1
23			1
24			1
25			1
26			1
27			1
28			X
29			1
30			1
31			1
32			X
33			1
34			2
35			X
36			1
37			X
38			X
39			1
40			1
41			1
42			1
43			1
44			1
45			X
46			1
47			1
48			X	28
49			1
50			1
51			1
52			1
53			1
54			X
55			X
56			X
57			1
58			X
59			1
60			1
61			1
62			1
63			1
64			X
65			1
66			1
67			1
68			1
69			2	21
70			1
71			1
72			1
73			1
74			1
75			1
76			1
77			1
78			X
79
80
81
82
83
Sheet1

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN>

 

[Kishan]

Okay, I think I have it... give this macro a try and let me know if it does what you want.

Hi Rick, thank you for reviewing the code and adding in it colours and format really appreciate very much, it is working fine with post#7 sample data no problem.</SPAN></SPAN>

When I applied to my original sheet with more than 9000 rows I found some place it miss the cycle mainly after when if the cycle gap is more than 100 </SPAN></SPAN>

As a can't post more than 100 lines so it will be the bit of trouble for you to copy paste column D through L under the column C one by one.</SPAN></SPAN>

Column C data ends at C97 if you paste D through L 9 column 91*9 will be 819 rows + 97 = 916 total rows </SPAN></SPAN>

Now if you run the code</SPAN></SPAN>

First cycle complete at row 62 periods 56 Ok</SPAN></SPAN>
Second cycle complete at row 90 periods 28 Ok</SPAN></SPAN>
Third cycle complete at row 146 periods 56 Ok</SPAN></SPAN>
Forth cycle complete at row 258 periods 112 Ok</SPAN></SPAN>

Fifth cycle must be complete at row 286 but no results and it continue fail the cycle </SPAN></SPAN>

Please could you check it?</SPAN></SPAN>

Data table</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5			P1
6			X
7			1	X	1	1	2	X	2	1	1	2
8			1	1	1	2	X	2	X	X	1	X
9			1	X	X	1	2	1	1	1	1	2
10			X	1	1	X	1	X	X	X	X	2
11			X	1	1	1	2	2	X	X	X	X
12			2	1	1	1	1	X	1	1	1	1
13			X	1	1	2	1	1	1	1	1	2
14			1	1	1	1	1	1	X	2	1	2
15			X	X	1	1	1	1	1	2	1	X
16			1	1	X	1	X	X	X	X	1	2
17			1	1	1	X	2	X	2	1	1	2
18			1	1	2	X	1	X	1	2	1	X
19			1	1	1	X	1	2	1	X	1	1
20			1	1	1	1	1	1	2	2	1	1
21			X	X	1	2	X	X	1	1	X	1
22			1	1	2	1	1	X	1	1	2	2
23			1	1	1	1	2	X	2	2	X	X
24			1	1	1	1	1	2	1	1	1	1
25			1	X	X	2	1	X	1	2	X	1
26			1	X	X	1	1	X	1	2	1	1
27			X	1	1	1	1	1	1	1	1	1
28			X	1	X	X	X	1	1	1	X	1
29			1	1	1	X	X	1	2	1	X	1
30			2	1	1	1	X	2	1	X	2	1
31			1	1	1	1	1	1	X	1	1	X
32			1	1	1	1	2	1	X	X	X	1
33			2	1	1	X	1	2	1	2	1	2
34			X	1	1	1	1	X	X	X	2	X
35			1	1	1	1	1	X	1	1	1	X
36			X	1	2	1	1	X	2	2	1	1
37			2	X	1	2	2	1	2	1	1	2
38			1	X	1	1	X	X	1	X	1	1
39			X	2	1	2	1	X	X	2	1	2
40			1	1	1	1	2	2	1	2	X	1
41			1	X	1	X	X	1	1	1	X	2
42			1	X	1	1	X	1	1	1	X	X
43			X	1	1	X	2	1	1	X	1	1
44			2	1	X	1	2	X	X	1	X	1
45			X	1	1	1	2	1	X	1	X	1
46			1	X	1	1	1	X	1	X	2	1
47			X	1	2	1	1	1	X	X	2	2
48			1	X	1	X	X	X	1	2	1	2
49			X	1	2	2	X	2	2	X	1	2
50			1	1	1	X	1	2	1	1	X	1
51			1	1	1	1	X	X	1	1	X	X
52			1	X	X	1	X	1	1	1	X	1
53			2	1	1	1	X	2	1	1	X	1
54			1	1	1	1	1	1	1	X	X	1
55			1	2	X	2	X	2	X	2	1	X
56			X	1	1	X	2	2	2	2	2	X
57			1	1	X	X	1	2	2	2	1	2
58			1	1	2	1	1	X	2	2	1	1
59			1	1	1	1	1	X	1	2	X	1
60			1	1	1	2	2	1	1	X	1	X
61			1	1	1	2	1	1	1	1	2	2
62			2	1	1	1	2	X	1	1	2	1
63			1	1	1	X	X	2	1	X	X	X
64			1	X	2	X	1	1	X	2	1	X
65			1	1	1	1	X	X	1	1	X	X
66			1	1	2	2	1	1	2	1	1	1
67			1	1	1	X	1	1	X	X	2	2
68			1	1	1	1	1	1	X	1	X	X
69			1	1	1	X	1	1	1	X	X	X
70			X	X	X	1	1	X	1	X	1	1
71			1	1	1	X	1	X	1	2	X	1
72			1	X	1	1	2	2	1	X	X	2
73			1	1	1	1	1	1	X	1	2	1
74			X	1	1	1	1	1	2	1	1	1
75			1	1	1	2	2	1	1	1	1	1
76			2	X	2	2	1	X	1	2	1	1
77			X	X	1	1	1	1	2	2	2	2
78			1	2	1	1	1	X	1	1	1	X
79			X	1	X	1	1	1	2	1	1	X
80			X	2	X	1	1	1	1	2	X	X
81			1	1	X	X	1	1	2	1	1	X
82			1	1	1	1	1	X	1	X	X	2
83			1	X	X	2	X	2	X	2	1	2
84			1	1	1	1	X	1	1	2	1	1
85			1	1	1	1	X	1	1	2	X	X
86			1	X	1	2	X	1	1	1	1	1
87			X	X	X	1	2	X	X	1	1	X
88			1	1	2	1	1	1	2	X	1	1
89			1	1	X	1	1	2	1	1	1	1
90			X	1	1	1	1	2	1	X	X	1
91			1	1	1	1	1	1	X	X	X	2
92			1	1	1	X	1	1	2	2	1	2
93			1	1	2	1	1	1	1	1	1	2
94			1	1	1	X	1	1	1	1	2	2
95			1	X	1	X	X	1	1	1	X	X
96			X	1	X	X	1	1	2	X	1	1
97			X	1	1	1	X	1	X	1	2	2
98
99
Sheet3-3

Thank you </SPAN></SPAN>

Regards</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Hi Rick, here is it come in my mind to show missing point hiding rows, cycle skip at line 286.</SPAN></SPAN>

Book1
	A	B	C	D	E
1
2
3
4
5			P1
6			X
62		7	2	56
90		7	X	28
146			2	56
258			2	112
259			1
260			1
261			X
262			X
263			X
264			1
265			X
266			1
267			1
268			1
269			X
270			2
271			X
272			1
273			1
274			1
275			2
276			1
277			1
278			X
279			1
280			1
281			2
282			1
283			X
284			1
285			1
286			2		Missed
287			1
288			1
289			1
290			X
291			X
292			X
293			1
294			2
295			1
296			1
297			1
298			2
299			1
300			1
301			X
302			X
303			1
304			1
305			1
306			X
307			1
308			1
309			1
310			2
311			1
312			2
313			1
314			X	56
315			1
316			X
317			1
318			1
319			1
320			1
321			X
322			2
323			X
324			1
325			1
326			1
327			1
328			2
329			X
330			X
331			1
332			1
333			2
334			2
335			1
336			X
337			X
338			1
339			2
340			X
341			1
342			X	28
343			1
Sheet1

Thank you </SPAN></SPAN>

Regards</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

I am not a coder but I observed that it check cycle "X12" as Dim is define, if cycle ends with 2, than it ignore first next cycle and jump to next cycle that's way miss match is happening my thought but not sure 100%

 

[Kishan]

I think VBA is ideal, but if not could I get any help with formula solution will do.</SPAN></SPAN>

Thank you </SPAN></SPAN>

Regards</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Bump

 

[Rick Rothstein]

Data table

	A	B	C	D	E	F	G	H	I	J	K	L	M	N

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FFFF, align: center"]P1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"]X[/TD]
[TD="bgcolor: #000000, align: center"]1[/TD]
[TD="bgcolor: #000000, align: center"]1[/TD]
[TD="bgcolor: #000000, align: center"]2[/TD]
[TD="bgcolor: #000000, align: center"]1[/TD]
[TD="bgcolor: #000000, align: center"]1[/TD]
[TD="bgcolor: #000000, align: center"]1[/TD]
[TD="bgcolor: #000000, align: center"]1[/TD]
[TD="bgcolor: #000000, align: center"]1[/TD]
[TD="bgcolor: #000000, align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]

[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3-3​

[/QUOTE]
Are you saying that you have multiple columns of values as shown above? If so, where are the numbers that the macro is generating supposed to be placed at?

Hi Rick, here is it come in my mind to show missing point hiding rows, cycle skip at line 286.

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FFFF, align: center"]P1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]62[/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="bgcolor: #000000, align: center"]2[/TD]
[TD="align: center"]56[/TD]
[TD="align: right"][/TD]

[TD="align: center"]90[/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="bgcolor: #000000, align: center"]X[/TD]
[TD="align: center"]28[/TD]
[TD="align: right"][/TD]

[TD="align: center"]146[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"]2[/TD]
[TD="align: center"]56[/TD]
[TD="align: right"][/TD]

[TD="align: center"]258[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"]2[/TD]
[TD="align: center"]112[/TD]
[TD="align: right"][/TD]

[TD="align: center"]259[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]260[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]261[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]262[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]263[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]264[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]265[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]266[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]267[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]268[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]269[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]270[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]271[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]272[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]273[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]274[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]275[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]276[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]277[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]278[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]279[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]280[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]281[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]282[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]283[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]284[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]285[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]286[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"]2[/TD]
[TD="bgcolor: #FF0000, align: center"][/TD]
[TD="bgcolor: #FF0000"]Missed[/TD]

[TD="align: center"]287[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]288[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

I may have misunderstood what you are trying to do. I assumed from your previously posted examples that the cycle had to be made from three consecutive black squares, but if I understand the above example correctly, that is not what you want; rather, it appears that the cycle completes as soon as and X, 1 and 2 occur even if those three black square are not consecutive... is that correct?

 

[Kishan]

Are you saying that you have multiple columns of values as shown above? If so, where are the numbers that the macro is generating supposed to be placed at?

</SPAN></SPAN>
Hi Rick,</SPAN></SPAN>
Post#11 I don't wanted result for multiple columns, as I wanted to show you 916 row in the column C, but I were not able to post so long data, that's why I posted in multiple columns format, so far you need to CUT column D through M one by one and past them below the column C to make one column only with 916 rows. I hope this is clear now.</SPAN></SPAN>

</SPAN></SPAN>
I may have misunderstood what you are trying to do. I assumed from your previously posted examples that the cycle had to be made from three consecutive black squares, but if I understand the above example correctly, that is not what you want; rather, it appears that the cycle completes as soon as and X, 1 and 2 occur even if those three black square are not consecutive... is that correct?

</SPAN></SPAN>
Rick, Post#12, which is perfect, I wanted cycle has to be occurred (completed) for 1, X, and 2, even if those three black square are not consecutive. </SPAN></SPAN>

Thank you </SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Hi Rick, may I clear my request this easy way to make easier to understand.</SPAN></SPAN>

For example live the cell C6, </SPAN></SPAN>

Start counting from cell C7 (1 to 7) example step 7 find "1" </SPAN></SPAN>
Go for next 7th cell find "1" again</SPAN></SPAN>
Go for next 7th cell find "1" again</SPAN></SPAN>
Go for next 7th cell find "X" again</SPAN></SPAN>
Go for next 7th cell find "1" again</SPAN></SPAN>
Go for next 7th cell find "2" Cycle has occurred (or can be said completed) 1, X and 2. Results in column D. (Reset search to zero)</SPAN></SPAN>


Now look continuations for next 7th step find "1", in the next 7th step "2", in the next 7th step "X", Cycle has occurred (or can be said completed) 1, X and 2. Results in column D. (Reset search to zero)</SPAN></SPAN>
</SPAN></SPAN>

Go for next occurrence in continuations of 1, X and 2 till occurred... and so on </SPAN></SPAN>

May this help</SPAN></SPAN>

Thank you </SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Rick Rothstein]

Start counting from cell C7 (1 to 7) example step 7 find "1"
Go for next 7th cell find "1" again
Go for next 7th cell find "1" again
Go for next 7th cell find "X" again
Go for next 7th cell find "1" again
Go for next 7th cell find "2" Cycle has occurred (or can be said completed) 1, X and 2. Results in column D. (Reset search to zero)

This did not really clear it up because the values making up the cycle are consecutive. This example would have been better...

Go for next 7th cell find "1" again
Go for next 7th cell find "X" again
Go for next 7th cell find "1" again
Go for next 7th cell find "1" again
Go for next 7th cell find "2" ... Cycle completes.

So you know, this is a little more complicated to figure out... I am working on it when I can.

 

[Kishan]

This did not really clear it up because the values making up the cycle are consecutive. This example would have been better...

Go for next 7th cell find "1" again
Go for next 7th cell find "X" again
Go for next 7th cell find "1" again
Go for next 7th cell find "1" again
Go for next 7th cell find "2" ... Cycle completes.

Yes Rick, I do agree your example is crystal clear. Thank you for it .</SPAN>

So you know, this is a little more complicated to figure out... I am working on it when I can.

</SPAN>

I also think this is not an easy really it a tricky task. Please make it when you can no hurry.</SPAN></SPAN>

Thank you Rick for all your help</SPAN></SPAN>

Have a nice weekend</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>