Check cycle of character 1X2

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,645
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDE
1
2
3
4
5P1
6X
71
8X
92
101
11X
121
131
14X
151
161
171
181
191
20214
211
221
231
241
251
261
271
28X
291
301
311
32X
331
342
35X
361
37X
38X
391
401
411
421
431
441
45X
461
471
48X28
491
501
511
521
531
54X
55X
56X
571
58X
591
601
611
621
631
64X
651
661
671
681
69221
701
711
721
731
741
751
761
771
78X
79
80
81
82
83
Sheet1


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

Regards,</SPAN>
Kishan</SPAN>
 
Last edited:
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
ABCDEFGHIJKLMN
1
2
3
4
5P1
6X
71X112X2112
81112X2XX1X
91XX1211112
10X11X1XXXX2
11X11122XXXX
1221111X1111
13X112111112
14111111X212
15XX1111121X
1611X1XXXX12
17111X2X2112
18112X1X121X
19111X121X11
201111112211
21XX12XX11X1
2211211X1122
2311112X22XX
241111121111
251XX21X12X1
261XX11X1211
27X111111111
28X1XXX111X1
29111XX121X1
302111X21X21
31111111X11X
32111121XXX1
33211X121212
34X1111XXX2X
3511111X111X
36X1211X2211
372X12212112
381X11XX1X11
39X2121XX212
4011112212X1
411X1XX111X2
421X11X111XX
43X11X211X11
4421X12XX1X1
45X11121X1X1
461X111X1X21
47X12111XX22
481X1XXX1212
49X122X22X12
50111X1211X1
511111XX11XX
521XX1X111X1
532111X211X1
541111111XX1
5512X2X2X21X
56X11X22222X
5711XX122212
5811211X2211
5911111X12X1
601112211X1X
611112111122
6221112X1121
63111XX21XXX
641X2X11X21X
651111XX11XX
661122112111
67111X11XX22
68111111X1XX
69111X111XXX
70XXX11X1X11
71111X1X12X1
721X11221XX2
73111111X121
74X111112111
751112211111
762X221X1211
77XX11112222
7812111X111X
79X1X111211X
80X2X11112XX
8111XX11211X
8211111X1XX2
831XX2X2X212
841111X11211
851111X112XX
861X12X11111
87XXX12XX11X
881121112X11
8911X1121111
90X111121XX1
91111111XXX2
92111X112212
931121111112
94111X111122
951X1XX111XX
96X1XX112X11
97X111X1X122
98
99
Sheet3-3


Thank you </SPAN></SPAN>

Regards</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi Rick, here is it come in my mind to show missing point hiding rows, cycle skip at line 286.</SPAN></SPAN>


Book1
ABCDE
1
2
3
4
5P1
6X
627256
907X28
146256
2582112
2591
2601
261X
262X
263X
2641
265X
2661
2671
2681
269X
2702
271X
2721
2731
2741
2752
2761
2771
278X
2791
2801
2812
2821
283X
2841
2851
2862Missed
2871
2881
2891
290X
291X
292X
2931
2942
2951
2961
2971
2982
2991
3001
301X
302X
3031
3041
3051
306X
3071
3081
3091
3102
3111
3122
3131
314X56
3151
316X
3171
3181
3191
3201
321X
3222
323X
3241
3251
3261
3271
3282
329X
330X
3311
3321
3332
3342
3351
336X
337X
3381
3392
340X
3411
342X28
3431
Sheet1


Thank you </SPAN></SPAN>

Regards</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:
Upvote 0
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%
 
Upvote 0
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>
 
Upvote 0
Data table
ABCDEFGHIJKLMN
1
2
3
4
5P1
6X
71X112X2112
81112X2XX1X
91XX1211112
10X11X1XXXX2
11X11122XXXX
1221111X1111
13X112111112
14111111X212
15XX1111121X

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

</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.
ABCDE
1
2
3
4
5P1
6X
627256
907X28
146256
2582112
2591
2601
261X
262X
263X
2641
265X
2661
2671
2681
269X
2702
271X
2721
2731
2741
2752
2761
2771
278X
2791
2801
2812
2821
283X
2841
2851
2862Missed
2871
2881

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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?
 
Last edited:
Upvote 0
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>
 
Upvote 0
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>
 
Last edited:
Upvote 0

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.
 
Last edited:
Upvote 0
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.
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>
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top