Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Trapper via OfficeKB.com
 
Posts: n/a
Default 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   Report Post  
N Harkawat
 
Posts: n/a
Default

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   Report Post  
Trapper via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Consecutive Cells Trapper via OfficeKB.com Excel Discussion (Misc queries) 6 May 24th 05 12:35 AM
copy every 30th cell in the column into consecutive cells in anoth shortcuts Excel Worksheet Functions 2 March 29th 05 07:28 AM
Populating consecutive columns from a static set of cells? Thomas Excel Discussion (Misc queries) 1 March 24th 05 06:39 PM
How do I set up a formula to repat 5 consecutive cells in a colum. jbsand1001 Excel Worksheet Functions 2 January 7th 05 09:36 PM
How do I set up a formula to repeat 5 consecutive cells in a colum jbsand1001 Excel Worksheet Functions 1 January 7th 05 06:59 PM


All times are GMT +1. The time now is 02:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"