Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Consecutive cells
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 |
#2
|
|||
|
|||
On cell B3 type this and copy it all the way down
=IF(A3=A2,IF(A3=A4,"","yes")) "Trapper via OfficeKB.com" wrote in message ... 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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
see response in .misc
-- HTH RP (remove nothere from the email address if mailing direct) "Trapper via OfficeKB.com" wrote in message ... 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. |
#5
|
|||
|
|||
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 On Mon, 23 May 2005 16:52:15 GMT, "Trapper via OfficeKB.com" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Consecutive Cells | Excel Discussion (Misc queries) | |||
copy every 30th cell in the column into consecutive cells in anoth | Excel Worksheet Functions | |||
Populating consecutive columns from a static set of cells? | Excel Discussion (Misc queries) | |||
How do I set up a formula to repat 5 consecutive cells in a colum. | Excel Worksheet Functions | |||
How do I set up a formula to repeat 5 consecutive cells in a colum | Excel Worksheet Functions |