I need a User Defined Function that will give me the maximum times the sequence, [ X (any value) Y], appears in a succession. Now the only thing that would break that streak of [X (any value) Y], would be for another set of values that contains for example [ X (any value) A], [X (any value) B], [X (any value) C], etc. So the first value in that sequence HAS to be [ X (any value) any value other than Y] in order to break the streak . So sequences such as [T (any value) B], [A (any value) C], etc are to be ignored. I an currently using 2007 version of excel.
1) X
2) A
3) Y
4) J
5) X
6) B
7) Y
8) X
9) C
10) Y
11) X
12) D
13) A
14) X
15) E
16) Y
17) B
18 ) N
19) X
20) V
21)Y
22) T
23) A
24) B
25) X
26) A
27) B
28) A
29) N
30) C
31) D
32) X
33) T
34) Y
35) X
36) X
37) Y
38) Y
In the above example:
Evaluating lines 1-38 the MaxStreak would be 3 (both lines 1-10 & 32-38)
(Note: The max would have been 8 if not for lines 11-13 & 25-27 breaking the streak.)
Evaluating lines 1-21 the MaxStreak would be also be 3 (lines 1-10)
(Note: The max would have been 5 if not for lines 11-13 breaking the streak.)
Evaluating lines 14-38 the MaxStreak would be 3 (lines 32-38)
(Note: The max would have been 5 if not for the lines 25-27 breaking the streak.)
Evaluating lines 11-24 the MaxStreak would be 2 (lines 14-21)
Evaluating lines 34-38 the MaxStreak would be 2
Also I would like this UDF to be flexible enough where I can input various sequences or a separate UDF that could evaluate sequences like the following below:
X
Y
B
Z
X
B
Y
Z
X
Y
B
C
Z
1) X
2) A
3) Y
4) J
5) X
6) B
7) Y
8) X
9) C
10) Y
11) X
12) D
13) A
14) X
15) E
16) Y
17) B
18 ) N
19) X
20) V
21)Y
22) T
23) A
24) B
25) X
26) A
27) B
28) A
29) N
30) C
31) D
32) X
33) T
34) Y
35) X
36) X
37) Y
38) Y
In the above example:
Evaluating lines 1-38 the MaxStreak would be 3 (both lines 1-10 & 32-38)
(Note: The max would have been 8 if not for lines 11-13 & 25-27 breaking the streak.)
Evaluating lines 1-21 the MaxStreak would be also be 3 (lines 1-10)
(Note: The max would have been 5 if not for lines 11-13 breaking the streak.)
Evaluating lines 14-38 the MaxStreak would be 3 (lines 32-38)
(Note: The max would have been 5 if not for the lines 25-27 breaking the streak.)
Evaluating lines 11-24 the MaxStreak would be 2 (lines 14-21)
Evaluating lines 34-38 the MaxStreak would be 2
Also I would like this UDF to be flexible enough where I can input various sequences or a separate UDF that could evaluate sequences like the following below:
X
Y
B
Z
X
B
Y
Z
X
Y
B
C
Z
Last edited: