pattern recognition?

[crullo]

Hi -
I have either an X or a Y in a column.
What I am looking for is a minimum of 3 X's in a row followed by a Y.
Example:
X
X
X
X
Y

I am hoping to have a function at bottom of my work sheet that will alert me to when this occurs.
Someone suggested that I can use a countif function, but I am not sure how to make it recognize when the "streak of X's" has been broken by a "Y".

Any suggestions are greatly appreciated!

Thank you

 

[crullo]

Is there a way to have this code apply only to certain columns?

there are a handful of columns where I am looking for this pattern (XXXY), but then there are certain columns that I am trying to get the reverse (YYYX).

Any way go set up a second code to look for "YYYX", and have it apply only to certain columns?

If someone can direct me to a tutorial or book on how to get started in VBA, Id love to try to figure out how to do this on my own (I have zero experience with code/VBA).

Thank you

 

[crullo]

I now understand. Please disregard my question a I have figured out how to tweak this code to spot other patterns in my spread-sheet.

THANKS

 

[Marcelo Branco]

You are welcome

Try this new version (easier to change the patterns you are looking for)

Function colPatternV2(r As Range, pat As String) As String
    If r.Rows.Count < Len(pat) Then Exit Function
    
    If UCase(Right(Join(Application.Transpose(r), ""), Len(pat))) = UCase(pat) Then
        colPatternV2 = "Break"
    End If
End Function

B1
=colPatternV2($A$1:A1,"xxxy")

or if you are looking for "yyyx"

=colPatternV2($A$1:A1,"yyyx")

M.

 

[crullo]

Thanks again Marcelo. I am still trying to learn exactly what that code means, but this has been a huge help.

One more question. Is it possible to add an additional parameter that would "skip-over" anytime a "P" appears?

Very rare, but on occassion in my data, I get:
XXPXY

Ideally whenever something like this happens, I would still want "break" to appear (as "P" is basically a worthless result for me).

It happens so infrequently that it is easy for me to spot.
But if there was an easy way to add an additional line of code to exclude the "P" it would be helpful.

Thank you

 

[Marcelo Branco]

Try this

Function colPatternV3(r As Range, pat As String) As String
    If r.Rows.Count < Len(pat) Or pat = "" Then Exit Function
    
    If UCase(Right(Application.Substitute(Join(Application.Transpose(r), "") _
        , "P", ""), Len(pat))) = UCase(pat) Then
        colPatternV3 = "Break"
    End If
End Function

 

[crullo]

Sir - Im not sure how to thank you enough. Hopefully some day I will be able to repay the favor!

 

[Marcelo Branco]

Sir - Im not sure how to thank you enough. Hopefully some day I will be able to repay the favor!

My pleasure! And thank you for your kind words.

M.

 

[crullo]

Excel Workbook
	A	B	R	W
16	11/5/2011	H	W
17	11/6/2011	H	W
19	11/11/2011	H	W
24	11/26/2011	H	W
28	12/5/2011	H	L	Break
29	12/8/2011	H	L	skip 1
30	12/10/2011	A
31	12/11/2011	H	W	skip 2
32	12/13/2011	H		OBSERVE
33	12/15/2011	A
34	12/17/2011	A
NYR

Excel 2007


Im back
Marcelo, and others, your macro's have worked great. But I think I can automate this further, and save some manual labor. (I started a thread last night, but clearly was not explaining what I was trying to do correctly!)

These next steps, I tried automating using Excel formula's, but no success.

using the above as an example, your macro enters "Break" in row 28.
I then manually enter "skip 1" and "skip 2" the next times that "H" appears in column "B" (regardless of what is in column "R").

Once a "break" and then "skip 1" "skip 2" are found, I want "Observe" entered into the next row that has "H" in Column "B".

I update this spread-sheet nightly. I basically want a heads-up at night when there is an "Observe" the next day.

This make any sense? Please let me know where I can clarify.

Thank you