Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?

You need a UDF. Call with the following

=Countalpha(B1,"S")

where B21 is the column Header and "S" is the character yo uare looking for.
You had spaces in the Column Header. I assumed your real column Headers did
not have the spaces.


Function CountAlpha(target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For i = 1 To Len(target)
ThisChar = Mid(target, i, 1)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next i
If MaxLen 1 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?

Hi Joel, it doesn't seem to be working as the result returned with 0 (i was
expecting 3 counts from the sample provided). See below sample column:

S
A
S
S
S
G
S
A
A
S
S
S
S
A
G
A
S
S
A
S

The above was placed in column B to test out the UDF. As the condition was
to track the aphabet "S" if it appears =2 (equal or greater than two) times,
the above sample had 3 instances (B3:B5, B10:B13, and B17:B18). Hence, it
should return a 3 in the formula cell that refers to the UDF. Is this
possible?

P.S. - I made a mistake in the original post of counting the result as 2
instances only when it should be 3 instead. Sorry.


"Joel" wrote:

You need a UDF. Call with the following

=Countalpha(B1,"S")

where B21 is the column Header and "S" is the character yo uare looking for.
You had spaces in the Column Header. I assumed your real column Headers did
not have the spaces.


Function CountAlpha(target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For i = 1 To Len(target)
ThisChar = Mid(target, i, 1)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next i
If MaxLen 1 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?

It wan't clear in the original posting that the data was in a range of cells.


=CountAlpha(B1:B20,"S")

Function CountAlpha(Target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For Each cell In Target
ThisChar = Trim(cell)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next cell
If MaxLen 2 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi Joel, it doesn't seem to be working as the result returned with 0 (i was
expecting 3 counts from the sample provided). See below sample column:

S
A
S
S
S
G
S
A
A
S
S
S
S
A
G
A
S
S
A
S

The above was placed in column B to test out the UDF. As the condition was
to track the aphabet "S" if it appears =2 (equal or greater than two) times,
the above sample had 3 instances (B3:B5, B10:B13, and B17:B18). Hence, it
should return a 3 in the formula cell that refers to the UDF. Is this
possible?

P.S. - I made a mistake in the original post of counting the result as 2
instances only when it should be 3 instead. Sorry.


"Joel" wrote:

You need a UDF. Call with the following

=Countalpha(B1,"S")

where B21 is the column Header and "S" is the character yo uare looking for.
You had spaces in the Column Header. I assumed your real column Headers did
not have the spaces.


Function CountAlpha(target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For i = 1 To Len(target)
ThisChar = Mid(target, i, 1)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next i
If MaxLen 1 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?

Almost there, works for anything greater than 2 but not equal to 2. Can i
just add the '=' sign beside ?

Thanks Joel!

"Joel" wrote:

It wan't clear in the original posting that the data was in a range of cells.


=CountAlpha(B1:B20,"S")

Function CountAlpha(Target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For Each cell In Target
ThisChar = Trim(cell)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next cell
If MaxLen 2 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi Joel, it doesn't seem to be working as the result returned with 0 (i was
expecting 3 counts from the sample provided). See below sample column:

S
A
S
S
S
G
S
A
A
S
S
S
S
A
G
A
S
S
A
S

The above was placed in column B to test out the UDF. As the condition was
to track the aphabet "S" if it appears =2 (equal or greater than two) times,
the above sample had 3 instances (B3:B5, B10:B13, and B17:B18). Hence, it
should return a 3 in the formula cell that refers to the UDF. Is this
possible?

P.S. - I made a mistake in the original post of counting the result as 2
instances only when it should be 3 instead. Sorry.


"Joel" wrote:

You need a UDF. Call with the following

=Countalpha(B1,"S")

where B21 is the column Header and "S" is the character yo uare looking for.
You had spaces in the Column Header. I assumed your real column Headers did
not have the spaces.


Function CountAlpha(target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For i = 1 To Len(target)
ThisChar = Mid(target, i, 1)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next i
If MaxLen 1 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?

By the way, is it possible to track 2 alphabets in a sequence? (i.e. perhaps
"S" and "G")

"andrew" wrote:

Almost there, works for anything greater than 2 but not equal to 2. Can i
just add the '=' sign beside ?

Thanks Joel!

"Joel" wrote:

It wan't clear in the original posting that the data was in a range of cells.


=CountAlpha(B1:B20,"S")

Function CountAlpha(Target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For Each cell In Target
ThisChar = Trim(cell)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next cell
If MaxLen 2 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi Joel, it doesn't seem to be working as the result returned with 0 (i was
expecting 3 counts from the sample provided). See below sample column:

S
A
S
S
S
G
S
A
A
S
S
S
S
A
G
A
S
S
A
S

The above was placed in column B to test out the UDF. As the condition was
to track the aphabet "S" if it appears =2 (equal or greater than two) times,
the above sample had 3 instances (B3:B5, B10:B13, and B17:B18). Hence, it
should return a 3 in the formula cell that refers to the UDF. Is this
possible?

P.S. - I made a mistake in the original post of counting the result as 2
instances only when it should be 3 instead. Sorry.


"Joel" wrote:

You need a UDF. Call with the following

=Countalpha(B1,"S")

where B21 is the column Header and "S" is the character yo uare looking for.
You had spaces in the Column Header. I assumed your real column Headers did
not have the spaces.


Function CountAlpha(target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For i = 1 To Len(target)
ThisChar = Mid(target, i, 1)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next i
If MaxLen 1 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?

andrew wrote...
Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

You're missing a grouping:

S A (S S S) G S A A (S S S S) A G A (S S) A S

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?

Would the latest sequence count for the sample data above really be 4,
or would it be 2?

If you want length of the last sequence of S's in the range D of
length greater than or equal to N, try the array formula

=LOOKUP(2,1/(MMULT(((MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S"))
=TRANSPOSE(MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S")))))*(D="S"),--
(D="S"))=N),
MMULT(((MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S"))
=TRANSPOSE(MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S")))))*(D="S"),--
(D="S")))

Hi Harlan, now i'm getting a bit confused. :-)
Anyway, perhaps lets try with a simpler version:

S
A
G
S
S
A
G
S
S
S
A

Assuming the text are all in 1 column (and the row keeps increasing weekly),
i would like to have a formula cell that calculates with the following
criterias:
1) FORMULA #1
if "S" appears =2 times in sequence (i.e. one after another), register
TRUE. In the above table, "S" appeared =2 times on 2 (two) occasions (row
A4&A5, then row A8-A10). The condition is to capture the most recent sequence
(i.e. row A8-A10) and display the result as (number of times it appeared in
latest sequence), which is three (3). Does this make sense?

2) FORMULA #2
If above is possible, then is it also possible to track two different text
which are in sequence (using the same condition as above)? Using above column
example, the text "S" and "A" together appeared in sequence three (3) times
(1st sequence from row A1-A2, then A4-A6, then A8-A11) - with the latest
showing a total of 4 (A8-A11) using the most recent sequence as a result in
the formula cell.

I hope it doesn't sound too confusing but i know you're near to getting it
perfect so don't give up pls.. :-)

"Harlan Grove" wrote:

andrew wrote...
Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

You're missing a grouping:

S A (S S S) G S A A (S S S S) A G A (S S) A S

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?

Would the latest sequence count for the sample data above really be 4,
or would it be 2?

If you want length of the last sequence of S's in the range D of
length greater than or equal to N, try the array formula

=LOOKUP(2,1/(MMULT(((MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S"))
=TRANSPOSE(MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S")))))*(D="S"),--
(D="S"))=N),
MMULT(((MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S"))
=TRANSPOSE(MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S")))))*(D="S"),--
(D="S")))