Find the number of text repeated in columns of Excel

[Jigisha]

I have an excel sheet. I want to find out the maximum number of times a particular text that 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 "B" has appeared continuously in a column without any other value in between. how do i do it in excel, any help is appreciated.

[GaganjyotTechie]

You can try the below macro code:

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

[Essien]

If you want to optionally allow for case sensitivity, then try the following code:

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

[Shurod]

Here's a formula; 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 brackets { }. Don't enter these brackets yourself, they are Excel's way of showing, that the formula is an array formula.

[Gania]

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 the 3 keys. If you copy the formula from the post, then select the cell you want the formula to reside in, click the formula bar and press Ctrl+v 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>.