Your issue has definitely crossed into User Defined Function territory. The
post by JMB is a good starting point. Let's see if he(she?) adjusts it to
meet your new requirements.

***********
Regards,
Ron

XL2002, WinXP


"Vasilis Tergen" wrote:

Sir,

Thank you very much for taking the time to write that formula.
I tried it repeatedly and these are its' results:

I) It works if the target column (Column A in this case) has less than
7 blank rows
at its' beginning. (Rows 1-7)
II) It may "jam" a high-powered machine if copied and pasted onto other
cells.
III) The function certainly works on the problem I posted, however
as it would've been too complicated to state in my initial
inquiry,
I didn't explain the complete version of the problem -which
follows-
and for which, the afforementioned formula does not suffice:

The Problem

A) Instead of having 1 column to work with, I have 3.
Larry, John or any other name, may appear either under columns F or H,
yet never
simultaneously, under both columns F & H of the same row.

B) Additionally, in order to count the number of absences, criteria must be
used,
from yet another column, column S.
Column S will always contain 1 of either of 3 values: A, B or C.

C) A visual table depiction:


Column F Column H Column S

Larry John A
Mary Mary A
Mary Larry B
John Larry C
Mary John C
Larry Mary A



D) The actual, exact data results needed are as follows:

I must count the maximum absence of:

1) "Larry" appearing under either column F or H, while
simultaneously,
having "A" appear under column S (on the same row).

2) "Larry" appearing under either column F or H, while
simultaneously,
having "B" appear under column S (on the same row).

3) "Larry" appearing under either column F or H, while
simultaneously,
having "C" appear under column S (on the same row).

- Obviously, 3 similar formulas will be used to calculate the
afforementioned,
entered into 3 separate cells.
- Note that the count must ommit rows which do not contain exactly:
(Ex) both: "Larry" and: ("A", or "B" or "C")
The formula you kindly provided me with, includes rows in its'
results, which do
not meet the specified criteria. It simply provides the maximum
absence between
such rows. Ex: In the target workbook, it resulted in a 1438
maximum row absence
between "Larry" appearing under column "F".

-To give you a better idea, "Larry" actually appears a total of
51 times in 12000 rows.
If I were to filter the 12000 rows for "Larry", I would find
that the maximum
absence of both "Larry" and "A" appearing on the same row,
would be= 15 times.
Not 1438, which is the maximum absence of "Larry" simply
appearing under column F.

F) Lastly, should it be of any help:
I recently posted a related question in this discussion forum.
It is to be found under: Excel worksheet functions/ "A rather
difficult .........."
I managed to answer that question on my own, succesfully.

If you were to help me say, define just one the aforementioned rows (in
excel terms)
I would more than likely be able to complete the needed formula.


Thank you very much.


"Ron Coderre" wrote:

Here you go.....
Try something like this:

With
A2:A12000 contains names, with "larry" interspersed

This ARRAY FORMULA returns the largest consecutive gap between "larry" cells
B1:
=MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Yeah....I know....it's not very elegant,
but it seems to get the job done. <g

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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