Count consecutive cells

  • Thread starter Thread starter Fred Smith
  • Start date Start date
F

Fred Smith

I have a table which tracks the attendance at an event over the years. Each
time someone attends, I enter 1 in the column for that year, otherwise
blank. I'd like to know the most consecutive events people have attended.
So, if I have rows like (with 'b' representing a blank cell):

Name1 b 1 1 1 b 1 1 1 1 b 1 b 1, most consecutive = 4
Name2 1 1 b b 1 b b b b 1 b 1 1, most consecutive = 2
Name3 1 1 1 1 1 1 b 1 1 1 1 b 1, most consecutive = 6
Name4 1 1 1 1 1 1 1 1 1 b 1 1 1, most consecutive = 9
Name5 b b b b b b 1 b 1 b 1 b b, most consecutive = 1

Is there a formula I can use to calculate the most consecutive times
attended?

Thanks,
Fred
 
Fred

There are probably other ways to do this but a User Defined Function could
suffice.

''to Find largest number of consecutive cells in a range which contain the
same string
''note syntax =findmax("string",range)

Function FindMax(MyLetter As String, myRange _
As Range) As Integer
Dim c As Range, TempMax As Integer, _
fReset As Boolean
For Each c In myRange.Cells
If c.Value = MyLetter Then
TempMax = TempMax + 1
Else
TempMax = 0
End If
FindMax = Application.WorksheetFunction _
.Max(FindMax, TempMax)
Next
End Function

Hit ALT + F11 to open Visual Basic Editor. Insert>Module. Copy/paste the
code above into this module.

ALT + Q to go back to Excel window.

Select a cell at end of data in row 1(say M1). Enter =FindMax(1,A1:L1)

OR if looking for text =FindMax("a",A1:L1)

You can copy this down column M.

Gord Dibben Excel MVP
 
Back
Top