Hi Vasilis

Assuming that my solution works i.e. does produce the correct answer for
Larry, there is no need to add 800 columns to the sheet at all.

On Sheet2, starting with A1, create a list of all 800 names going down
to cell A800
Then, run the following trivial piece of code to obtain the result for
all 800 people

Sub FindLarry()
Dim i As Long, lrow As Long

lrow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lrow
Sheets("Sheet1").Range("X1") = Sheets("Sheet2").Cells(i, "A")
Sheets("Sheet2").Cells(i, "B") = Sheets("Sheet1").Range("Y1")
Next

End Sub

--
Regards

Roger Govier


"Vasilis Tergen" wrote in
message ...
Sir,

As stated in the initial post (question), helper columns cannot be
used
because
I'm analyzing 800 names such as "Larry", "John"... over 12000 rows.
This, would require using: 800* 2 additional helper columns= 1600 *
12000*
22 columns (the width of the report table)= 422.400.000 cells
containing
formulas.
(Data-rows- are also added daily, to the existing, 12.000...)



"Roger Govier" wrote:

Hi

If I understand you correctly, then with the use of 2 helper columns
you
could do the following.
I used columns X and Y
In X1 I entered Larry and in X2
=OR(F2=$F$1,H2=$X$1)*OR(S2={"A","B","C"})*ROW()
In Y2
=X2-MAX($X$2:X2)

In both cases, fill down as required
in Y1
=MAX(Y:Y) returns the maximum you are seeking

--
Regards

Roger Govier


"Vasilis Tergen" wrote in
message ...
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