Counting consecutive text

[paulontour]

Hi

I have a column which has it in

Unclear
Suitable
Unclear
Unclear
Suitable
Suitable
Suitable etc etc

I would like the cell at the very end of the column (or begining) to say PASS when 3 consecutive suitables appear.

Is this possible?

Thanks
Paul.

 

[machopicho]

Try this personalized function in Vba module:

Function COUNT_WORD(rng As Range, whichword As String, result As String)
For Each cll In rng
If cll = whichword And cll.Offset(1, 0) = whichword And _
cll.Offset(2, 0) = whichword Then
COUNT_WORD = result
Exit Function
End If
Next
End Function

The function ha 3 parameters:
rng = your range where find whichword (in your case is Suitable)
whichword = word to search
result = expected result

 

[SteveO59L]

Nowhere near as elegant

Private Sub Worksheet_Change(ByVal Target As Range)
Dim count As Integer
Dim c As Range, rng
If Target.Column = 1 Then
Range("b1").ClearContents
Set rng = Range("a1:a" & Target.Row)
For Each c In rng
If Target.Value = "Suitable" Then
count = count + 1
If count = 3 Then
Range("b1") = "Pass at row " & Target.Row
End If
Else
count = 0
End If
Next c
End If
End Sub