Do i still use this formula in the target cell?

=if(CountAlpha(B1:B20,"S")
CountAlpha(B1:B20,"G"),CountAlpha(B1:B20,"S"),Coun tAlpha(B1:B20,"G"))


"Joel" wrote:

I didn't initially understand what you wanted but thinking about it for a
while I figured out what you were really asking and had the solution in my
head waiting for your latest request.

Function CountAlpha(Target As Range, Alpha As String)

UAlpha = UCase(Alpha)
AlphaLen = Len(Alpha)

MaxLen = 0
StrCount = 0

'Put input data into a string
For Each cell In Target
InputStr = InputStr & cell.Value
Next cell
InputStr = UCase(InputStr)

Position = 1
Do While Position <= Len(InputStr) - AlphaLen + 1

If Mid(InputStr, Position, AlphaLen) = UAlpha Then

StrCount = StrCount + 1
Position = Position + AlphaLen
Else
If StrCount MaxLen Then
MaxLen = StrCount
End If
StrCount = 0
Position = Position + 1
End If
Loop

If StrCount MaxLen Then
MaxLen = StrCount
End If

If MaxLen = 2 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi Joel, the formula should return one value as what you've said. However,
what i actually wanted was for the script (UDF) to check for 2 alphabets
which are in sequence (e.g. GSS, SGGG or SSGSS as examples in the table
provided) instead of just one alphabet previously. So, whenever the UDF
detects a sequence it will count as one instance - and keeps adding whenever
another occurs.

"Joel" wrote:

Yes you can change 2 ro = 2. Functions can only return one value so I'm
not sure how youwould track to different values.

=if(CountAlpha(B1:B20,"S")
CountAlpha(B1:B20,"G"),CountAlpha(B1:B20,"S"),Coun tAlpha(B1:B20,"G"))


"andrew" wrote:

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?