Help with User Defined Function

[tlinht]

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

 

[jasonb75]

This is a follow up to thread http://www.mrexcel.com/forum/excel-questions/796121-consecutive-appearance.html

 

[VoG]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).