Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a twist on a VLOOKUP? | Excel Discussion (Misc queries) | |||
Fill Down .. with a twist | Excel Discussion (Misc queries) | |||
Cut / Paste with a Twist | Excel Discussion (Misc queries) | |||
Sumif with a twist? | Excel Worksheet Functions | |||
Vlookup With A Twist | Excel Worksheet Functions |