Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay

Hi Jay,

Try:

'=============
Public Function ConsecutiveCount(sStr As String, rng As Range) As Long
Dim rCell As Range
Dim iCtr As Long, jCtr As Long

For Each rCell In rng.Cells
With rCell
If .Value = sStr Then
iCtr = iCtr + 1
jCtr = Application.Max(jCtr, iCtr)
Else
iCtr = 0
End If
End With
Next rCell
ConsecutiveCount = jCtr
End Function
'<<=============


---
Regards,
Norman



"Jay" wrote in message
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay

Hi Jay,

To optionally allow for case sensitivity, try the following version:

'=============
Public Function ConsecutiveCount(sStr As String, rng As Range, _
Optional blCaseSensitive As Boolean = False) As Long
Dim rCell As Range
Dim iCtr As Long, jCtr As Long

For Each rCell In rng.Cells
With rCell
If blCaseSensitive Then
If .Value = sStr Then
iCtr = iCtr + 1
jCtr = Application.Max(jCtr, iCtr)
Else
iCtr = 0
End If
Else
If UCase(.Value) = UCase(sStr) Then
iCtr = iCtr + 1
jCtr = Application.Max(jCtr, iCtr)
Else
iCtr = 0
End If
End If
End With
Next rCell
ConsecutiveCount = jCtr
End Function
'<<=============


---
Regards,
Norman

"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Leo thanx for your reply

but i m not able to execute the problem am getting error

table is

A
A
A
B
A
B
B
B
B
A

need to find max how many times the B has appeared continuously with
out any break

abvoe case B appeared 4 times in a continously .. this has to be
displyed on other cell.
where should i put that formula in ?

regards
Jay
Leo Heuser wrote
:
"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Hi Jay

enter "b" (or "B") without quotes in F1, and enter
the formula in any cell you want. Remember to finish
the formula with <Shift<Ctrl<Enter (all 3 keys
pressed at the same time i.e. press <Shift and <Ctrl
and while holding them press <Enter). Release all
3 keys.
If you copy the formula from the post, select
the cell you want the formula to reside in, click the formula
bar and press <Ctrlv to paste it to the bar.
Go to the end of the first line and press <Delete to connect
the 2 lines and finish with <Shift<Ctrl<Enter

Leo Heuser

Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
Leo thanx for your reply

but i m not able to execute the problem am getting error

table is

A
A
A
B
A
B
B
B
B
A

need to find max how many times the B has appeared continuously with
out any break

abvoe case B appeared 4 times in a continously .. this has to be
displyed on other cell.
where should i put that formula in ?

regards
Jay
Leo Heuser wrote
:
"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and
the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're
Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Leo

still its giving error...

I entered the way you mentioned here changing the F1 and Cell Range
from A1:A25 to L9:L64999

do i need to change anything in the quote

looking forward for ur reply
regards
Jay
Leo Heuser wrote:
Hi Jay

enter "b" (or "B") without quotes in F1, and enter
the formula in any cell you want. Remember to finish
the formula with <Shift<Ctrl<Enter (all 3 keys
pressed at the same time i.e. press <Shift and <Ctrl
and while holding them press <Enter). Release all
3 keys.
If you copy the formula from the post, select
the cell you want the formula to reside in, click the formula
bar and press <Ctrlv to paste it to the bar.
Go to the end of the first line and press <Delete to connect
the 2 lines and finish with <Shift<Ctrl<Enter

Leo Heuser

Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
Leo thanx for your reply

but i m not able to execute the problem am getting error

table is

A
A
A
B
A
B
B
B
B
A

need to find max how many times the B has appeared continuously with
out any break

abvoe case B appeared 4 times in a continously .. this has to be
displyed on other cell.
where should i put that formula in ?

regards
Jay
Leo Heuser wrote
:
"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and
the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're
Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

here how i entered the forumula

MAX(FREQUENCY(IF(L9:L64999=B,COUNTIF(OFFSET(L9,,,R OW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&B)),ROW(IN DIRECT("1:"&ROWS(L9:L64999)))-1))

regrds

Jay


Jay wrote:
Leo

still its giving error...

I entered the way you mentioned here changing the F1 and Cell Range
from A1:A25 to L9:L64999

do i need to change anything in the quote

looking forward for ur reply
regards
Jay
Leo Heuser wrote:
Hi Jay

enter "b" (or "B") without quotes in F1, and enter
the formula in any cell you want. Remember to finish
the formula with <Shift<Ctrl<Enter (all 3 keys
pressed at the same time i.e. press <Shift and <Ctrl
and while holding them press <Enter). Release all
3 keys.
If you copy the formula from the post, select
the cell you want the formula to reside in, click the formula
bar and press <Ctrlv to paste it to the bar.
Go to the end of the first line and press <Delete to connect
the 2 lines and finish with <Shift<Ctrl<Enter

Leo Heuser

Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
Leo thanx for your reply

but i m not able to execute the problem am getting error

table is

A
A
A
B
A
B
B
B
B
A

need to find max how many times the B has appeared continuously with
out any break

abvoe case B appeared 4 times in a continously .. this has to be
displyed on other cell.
where should i put that formula in ?

regards
Jay
Leo Heuser wrote
:
"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and
the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're
Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

"Jay" skrev i en meddelelse
oups.com...
Leo

still its giving error...

I entered the way you mentioned here changing the F1 and Cell Range
from A1:A25 to L9:L64999

do i need to change anything in the quote

looking forward for ur reply
regards
Jay


Hi Jay

You are welcome to attach a copy of your workbook to
a personal mail, and I'll take a look at it. Which version
of Excel are you using?

leo.heuser at adslhome.dk

Regards
Leo Heuser