Count the period once cycle is completed

[Kishan]

Hi,

This query is bit complicated I will try my best to explain it

Data are located in cells C6:I82 what I need to check cycle for 1, X & 2 among the 7 columns C through I one by one and count the max numbers where the cycle is completed within the 7 column and the count result show in column J

For example...

1st cycle of 1, X, 2 has been completed with in 27 periods in column I so far result in Cell I32 = 27

2nd cycle of 1, X, 2 has been completed with in 30 periods in column G so far result in Cell J62 = 30

And continue finding...

Column K shows only the summary of cycle counts.

Colours are filled just to shown to show example bit clearer.

Example data...

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

Thank you in advance

Kishan

 

[mrmmickle1]

Re: Count the period once cycle is completed tricky one

Try something like this UDF :

Function CycleCount(ColStart As Integer, ColStop As Integer, RowStart As Integer, RowStop As Integer)
    
    Dim RowLp As Integer
    Dim ColLp As Integer
    Dim CyclecountFinal As Integer
    Dim PrevCycleCountVal As Integer
    Dim myVar As String
    
    For ColLp = ColStart To ColStop
        For RowLp = RowStart To RowStop
            myVar = myVar & Cells(RowLp, ColLp) 'Concatenate all of the values together
        Next RowLp
            X = InStr(1, myVar, "X"): One = InStr(1, myVar, "1"): Two = InStr(1, myVar, "2") 'Define First Row containing each string
            myRow = Application.Max(X, One, Two) 'Get Max Row
                If myRow > PrevCycleCountVal Then
                    CyclecountFinal = myRow 'Overwrite value if new column has greater value
                Else
                    'Do Nothing if number is not larger....
                End If
            PrevCycleCountVal = myRow
            myVar = vbNullString
    Next ColLp
    
    CycleCount = CyclecountFinal

End Function

Use it in a worksheet formula like this:

=CycleCount(3,9,6,32) This will yield 27
=CycleCount(3,9,33,62) This will yield 30
=CycleCount(3,9,63,82) This will yield 15

 

[Kishan]

Re: Count the period once cycle is completed tricky one

Try something like this UDF

Hi Matt Mickle, thank you for the function please can you explain may I do not understand how do I identify the period of cycle 1, X & 2 is completed in the all 7 columns.

The results are showing in the column J it is manually calculated I need code to look in the range and find the period of the cycle completed and show the results in the column J as shown


Kind Regards,
Kishan

 

[Eric W]

Re: Count the period once cycle is completed tricky one

You can use a variation of the formula I provided earlier:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4
5			C1	C2	C3	C4	C5	C6	C7		Count Cycle	C1	C2	C3	C4	C5	C6	C7
6			X	1	1	1	2	X	X		27	5	6	11	11	9	4	27
7			X	2	X	1	2	1	1		30	5	7	13	3	30	7	6
8			X	1	1	1	2	X	X		15	15	4	10	3	13	15	7
9			2	1	1	1	1	2	1			#N/A	4	#N/A	#N/A	#N/A	#N/A	#N/A
10			1	1	1	1	1	X	1			#N/A	4	#N/A	#N/A	#N/A	#N/A	#N/A
11			X	X	X	1	2	1	1			#N/A	4	#N/A	#N/A	#N/A	#N/A	#N/A
12			X	1	1	2	1	1	X

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

Sheet17​

Worksheet Formulas Cell Formula K6 =IF(ISERROR(MAX(L6:R6)),"",MAX(L6:R6)) L6 =MAX(MATCH("X",OFFSET(C$6:C$66,SUM($K$5:$K5),0),0),MATCH(1,OFFSET(C$6:C$66,SUM($K$5:$K5),0),0),MATCH(2,OFFSET(C$6:C$66,SUM($K$5:$K5),0),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put the L6 formula in, and copy to R6, then down the columns as needed. Put the K6 formula in and drag down as needed. You can hide the M:R columns if you want.

 

[Kishan]

Re: Count the period once cycle is completed tricky one

You can use a variation of the formula I provided earlier:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4
5			C1	C2	C3	C4	C5	C6	C7		Count Cycle	C1	C2	C3	C4	C5	C6	C7
6			X	1	1	1	2	X	X		27	5	6	11	11	9	4	27
7			X	2	X	1	2	1	1		30	5	7	13	3	30	7	6
8			X	1	1	1	2	X	X		15	15	4	10	3	13	15	7
9			2	1	1	1	1	2	1			#N/A	4	#N/A	#N/A	#N/A	#N/A	#N/A
10			1	1	1	1	1	X	1			#N/A	4	#N/A	#N/A	#N/A	#N/A	#N/A
11			X	X	X	1	2	1	1			#N/A	4	#N/A	#N/A	#N/A	#N/A	#N/A
12			X	1	1	2	1	1	X

<tbody>
</tbody>

Sheet17​

Worksheet Formulas Cell Formula K6 =IF(ISERROR(MAX(L6:R6)),"",MAX(L6:R6)) L6 =MAX(MATCH("X",OFFSET(C$6:C$66,SUM($K$5:$K5),0),0),MATCH(1,OFFSET(C$6:C$66,SUM($K$5:$K5),0),0),MATCH(2,OFFSET(C$6:C$66,SUM($K$5:$K5),0),0)) <tbody> </tbody>

<tbody>
</tbody>

Put the L6 formula in, and copy to R6, then down the columns as needed. Put the K6 formula in and drag down as needed. You can hide the M:R columns if you want.

Hi Eric, ok I understand thank you for the explanation this scan each row and show the results in the corresponding column period of the cycle has completed that is perfect.

One more question now we know period of cycle results are 27, 30, 15 now...

How can I place 27 in cell J32 = 27 to identify that 1st cycle is finished in the row 32.
And then 30 in the cell J62 = 30 to identify that 2nd cycle is finished in the row 62.
And then 15 in the cell J77 = 15 to identify that 3rd cycle is finished in the row 77.

And so on...

Please is it possible to do

Thank you once again for your help

Kind Regards,
Kishan

 

[Eric W]

Re: Count the period once cycle is completed tricky one

Sticking with formulas, put this in J6 and drag down:

=IF(ROWS($J$6:$J6)=SUM(OFFSET($K$6,0,0,COUNT($J$5:$J5)+1)),OFFSET($K$6,COUNT($J$5:$J5),0),"")

A macro can do this too.

 

[Kishan]

Re: Count the period once cycle is completed tricky one

Sticking with formulas, put this in J6 and drag down:

=IF(ROWS($J$6:$J6)=SUM(OFFSET($K$6,0,0,COUNT($J$5:$J5)+1)),OFFSET($K$6,COUNT($J$5:$J5),0),"")

A macro can do this too.

Hi Eric, This is Amazing!! working like a charm. If this could be done by the macro I will prefer please if you could do the macro for me. In this case we do not need the helper columns.
I really do want only the results for periods in column J where in the row cycle is completed.

Thank you

Kind Regards,
Kishan

 

[Eric W]

Re: Count the period once cycle is completed tricky one

If you want to do without the helper columns, put a 0 in C5, then put this charming formula in C6:

=IF(PRODUCT(MMULT(TRANSPOSE(ROW((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6))^0),IF((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6)=1,1,0))*MMULT(TRANSPOSE(ROW((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6))^0),IF((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6)=2,1,0))*MMULT(TRANSPOSE(ROW((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6))^0),IF((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6)="X",1,0))),ROW(J6)-LOOKUP(2,1/($J$1:$J5<>""),ROW($J$1:$J5)),"")

confirmed with Control+Shift+Enter. Then drag down the column. If you still want a macro, I'll take a look at it tomorrow.

 

[Kishan]

Re: Count the period once cycle is completed tricky one

If you want to do without the helper columns, put a 0 in C5, then put this charming formula in C6:

=IF(PRODUCT(MMULT(TRANSPOSE(ROW((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6))^0),IF((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6)=1,1,0))*MMULT(TRANSPOSE(ROW((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6))^0),IF((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6)=2,1,0))*MMULT(TRANSPOSE(ROW((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6))^0),IF((INDEX($C$1:$C$1000,LOOKUP(2,1/($J$1:$J5<>""),ROW($J$2:$J6))):I6)="X",1,0))),ROW(J6)-LOOKUP(2,1/($J$1:$J5<>""),ROW($J$1:$J5)),"")

confirmed with Control+Shift+Enter. Then drag down the column. If you still want a macro, I'll take a look at it tomorrow.

Wow Eric, how you have written such a huge formula it is working as request.

I do appreciate your help very much and thanks for spending your precious time.

Please if you could write a macro I would like to have it.

Good Luck

Kind Regards,
Kishan

 

[Kishan]

Re: Count the period once cycle is completed tricky one

Hi Eric,

If it is not a much trouble for you kindly could you make a 2 macro one for Post#4 to identify the column & another for post#8 to identify the row please

Thank you for your time.

Kind Regards,
Kishan