Consecutive cells

[Trapper via OfficeKB.com]

Sorry this seems to be a repetitive question but I can't seem to get it to
work. I have once colum with with either an O or a U in it. What I want
to do is have EXCEL tell me when there are consecutive occurences of either
of these letters:
EXAMPLE:
A B
1 O
2 O YES
3 U
4 O
5 U
6 U
7 U YES

What do I need to do to tell me that 2 consecutive O's have occurred and 3
consecutive U have occured, by placing a YES in Column B. Can this be done?
I also want to know the max number of consecutives. In this example it
would be 2 O's and 3 U's

 

[N Harkawat]

On cell B3 type this and copy it all the way down
=IF(A3=A2,IF(A3=A4,"","yes"))

 

[Trapper via OfficeKB.com]

Thanks for the quick response! It is almost what I need, i think it's the
right formula but the result was not quite what I was expecting. I am
thinking a different approacch would help. What formula would I use to
just tell me how many times either O or U, whichever I choose, occurs
consecutively. I.E. how many time does O appear 2 times in a row, or 3
times in a row, or 4 times in a row.

 

[Bob Phillips]

see response in .misc

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

Trapper

The COUNTIF will give you the number of times consecutives occur.

This UDF will find just the maximum consecutive letters in a range.

Not quite what you want but close?

Option Compare Text
Function FindMax(MyLetter As String, myRange _
As Range) As Integer
Dim c As Range, TempMax As Integer, _
fReset As Boolean
For Each c In myRange.Cells
If c.Value Like MyLetter Then
TempMax = TempMax + 1
Else
TempMax = 0
End If
FindMax = Application.WorksheetFunction _
.Max(FindMax, TempMax)
Next
End Function

Usage is: =FindMax("u",range)


Gord Dibben Excel MVP