A UDF that appears to work okay:

syntax is:
=maxinterval(A1:A12000,"larry")



Function MaxInterval(rngData As Range, _
varCriteria As Variant) As Long
Dim lngLast As Long
Dim i As Long

lngLast = 0

For i = 1 To rngData.Rows.Count
If rngData(i, 1) = varCriteria Then
MaxInterval = Application.Max(MaxInterval, i - lngLast - 1)
lngLast = i
ElseIf i = rngData.Rows.Count Then
MaxInterval = Application.Max(MaxInterval, i - lngLast)
End If
Next i

End Function



"Vasilis Tergen" wrote:

How would I find the maximum amount of intervals between
"Larry" re- appearing in column A?
I must use a formula- not a macro or a filter.

The correct answer in this example= 4.
That means that in column A, the maximum absence (or intervals)
of "Larry" re- appearing was 4 times, which occured between rows
2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once)


Rows Column A

1 John
2 Larry
3 John
4 John
5 John
6 Mary
7 Larry
8 Mary
9 Larry
10 John