#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Sum with a twist

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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Sum with a twist

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?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Sum with a twist

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?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Sum with a twist

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?

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Sum with a twist

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?



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Sum with a twist

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?

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Sum with a twist

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?

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Sum with a twist

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?

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Sum with a twist

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?

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Sum with a twist

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?



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Sum with a twist

Yes, I just made changes so the search string (2nd parameter) can be multiple
characters like "SOS".

"andrew" wrote:

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?

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Sum with a twist

Thanks Joel!

By the way, can i have more than one UDF CountAlpha function running? The
current one looks for =2. What if i need to have separate formula cell to
look for =3 and =4 (either for 1 text character or 2 text character per
below)?

"Joel" wrote:

Yes, I just made changes so the search string (2nd parameter) can be multiple
characters like "SOS".

"andrew" wrote:

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?

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,231
Default Sum with a twist

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")))
  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,231
Default Sum with a twist

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.

....
..S
..A
..S
..S
..S
..G
..S
..A
..A
..S
..S
..S
..S
..A
..G
..A
..S
..S
..A
..S

....

So do you mean your data is in multiple rows in a single column and
there's only 1 letter in each row (cell)? In which case, what you want
to identify is subranges that match GSS, SGGG or SSGSS, so that the
first of these would be G in one cell and S in the next two cells
below it, the second would be S in one cell and G in the next three
cells below it, and the last of these would be S in two adjacent cells
followed by G in the next cell and S in the next two cells?
  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Sum with a twist

I added a 3rd parameter to the function for the PassCriteria rather than
writing a new function. Modify your call to the function by adding new
parameter.

You can call a function multiple times from the spreadsheet like you did
with no problems. the functtions do not run at the same time by are called
in order usually from left to right unless you have parenthesis to change the
precedence of the calling order.

Function CountAlpha(Target As Range, Alpha As String, PassCriteria as Integer)

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 = PassCriteria Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Thanks Joel!

By the way, can i have more than one UDF CountAlpha function running? The
current one looks for =2. What if i need to have separate formula cell to
look for =3 and =4 (either for 1 text character or 2 text character per
below)?

"Joel" wrote:

Yes, I just made changes so the search string (2nd parameter) can be multiple
characters like "SOS".

"andrew" wrote:

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?



  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Sum with a twist

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")))

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
a twist on a VLOOKUP? Valerie Excel Discussion (Misc queries) 12 August 15th 07 01:14 PM
Fill Down .. with a twist mmb Excel Discussion (Misc queries) 1 August 28th 06 04:03 PM
Cut / Paste with a Twist [email protected] Excel Discussion (Misc queries) 3 July 10th 06 09:51 PM
Sumif with a twist? Bruce Excel Worksheet Functions 1 August 16th 05 10:57 PM
Vlookup With A Twist nebb Excel Worksheet Functions 2 July 16th 05 04:39 AM


All times are GMT +1. The time now is 06:53 AM.

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"