Consecutive cells

  • Thread starter Thread starter Trapper via OfficeKB.com
  • Start date Start date
T

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
 
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.
 
see response in .misc

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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
 
Back
Top