[Vasilis Tergen]

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

[Bernard Liengme]

I can do it with a helper column
With the names in A1:A7
In B1 enter: =--(A1="Larry")
In B2 enter: =(B1+(A2<"Larry"))*(A2<"Larry")
Copy this down the column
=MAX(B1:B7) returns the value 4
So experimenting with an array formula, this seems to work
=MAX((B1:B6+(A2:A7<"Larry"))*(A2:A7<"Larry"))
entered with CTRL+SHIFT+ENTER
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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

[Vasilis Tergen]

Sir,

Thank you for the reply, it is greatly appreciated.
However, the data table I'm using, contains 12.000 rows of data and
800 names such as "Larry", "John"...
I did something similar to what you've suggested previously, which
unfortunately
resulted in "freezing" the computer, as I needed:
800 names* 12.000 rows containing a "helper" column to analyze the data.
Thus, I'm looking for a formula to be entered in a single cell.

"Bernard Liengme" wrote:

I can do it with a helper column
With the names in A1:A7
In B1 enter: =--(A1="Larry")
In B2 enter: =(B1+(A2<"Larry"))*(A2<"Larry")
Copy this down the column
=MAX(B1:B7) returns the value 4
So experimenting with an array formula, this seems to work
=MAX((B1:B6+(A2:A7<"Larry"))*(A2:A7<"Larry"))
entered with CTRL+SHIFT+ENTER
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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

[Ron Coderre]

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

[Vasilis Tergen]

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

[Ron Coderre]

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

[Vasilis Tergen]

Sir,

I appreciate the reply.
I am at the same time somewhat dissapointed however, as your estimation of
the
problem requiring a UDF, shatters any hopes I may have had, of solving the
problem
without one.
I was previously pressed to find an answer using existing functions.
Now I am pressed to wait upon others' replies or learn programming myself...

"Ron Coderre" wrote:

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

[Roger Govier]

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

[Vasilis Tergen]

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

[Roger Govier]

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

[Lori]

Try array-entered (CSE):

=MAX(FREQUENCY(ROW(1:1201),IF((F1:F1200="Larry")+( H1:H1200="Larry"))*
(S1:S1200="A"),ROW(1:1200))))-1

then eplace "A" by "B" or "C" for other results.
Note: there is one extra row in the first argument of the frequency
function


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

[Vasilis Tergen]

Thank you for the reply

I tried the formula repeatedly and its' results are as follows:

A) It creates no "freezing" problems whatsoever, due to calculation load,
because of its' simplicity.
B) It doesn't count the needed absences. It instead counts a volatile
frequency between rows containing both "Larry" and "A".
Ex: It will result in a count of 1158 absences (between rows containing
both "Larry" and "A", searching through 12000 rows.
(Your formula had to be slightly altered to produce this result)
C) Again, to fully grasp the concept, imagine that you filter the 12000 rows
for
the name "Larry". Then, 51 rows would remain.
Within those 51 rows, the maximum absence of both:
a) "Larry" appearing under columns "F" or "H"
&
b) "A" appearing under column "S" (on the same row),

would be easily determined as being= 15 times.

Once again, thank you for the much needed assistance.

"Lori" wrote:

Try array-entered (CSE):

=MAX(FREQUENCY(ROW(1:1201),IF((F1:F1200="Larry")+( H1:H1200="Larry"))*
(S1:S1200="A"),ROW(1:1200))))-1

then eplace "A" by "B" or "C" for other results.
Note: there is one extra row in the first argument of the frequency
function


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

[JMB]

Another formula solution - but not practical with the number of cells you're
trying to calculate (and also array entered):

=MAX(IF(COUNTIF(A1:A12000,"larry")1,LARGE((A1:A12 000="larry")*ROW(A1:A12000),ROW(INDIRECT("1:"&COUN TIF(A1:A12000,"larry")-1)))-LARGE((A1:A12000="larry")*(ROW(A1:A12000)),ROW(IND IRECT("2:"&COUNTIF(A1:A12000,"larry"))))-1),IF(A1<"larry",MATCH("larry",A1:A12000,0)-1),IF(A12000<"larry",ROWS(A1:A12000)+ROW(A1)-1-MAX(IF(A1:A12000="larry",ROW(A1:A12000),""))))


Change the range reference as needed. It takes a little bit to calculate
(but still under a minute on my machine). Without using any helper columns
or VBA- you almost have to use an array formula, which will be quite a
calculation load. If you have to do all 800 names, I'd look at using a
custom 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

[Vasilis Tergen]

Sir,

Your reply is greatly appreciated.

I invoked the formula you provided repeatedly.
Unfortunately, its' calculation result is not the desired one.
A) In a blank worksheet, containing only column A,
which in turn, contains "Larry" & other, various names, totalling 29
rows,
it results in a "11971" count.
B) In the target workbook, the results are of even greater disparity.
C) I can't be entirely certain as of the error in calculation, as the
formula in
question repeats COUNTIF commands, using discretionary (& or volatile)
references.

P.S. I am sorry I couldn't reply sooner. However, I had (& have) to
reply to all others which graciously offered a reply to this
post, in the order
that they did. (Including thoroughly testing their replies
respectively)


"JMB" wrote:

Another formula solution - but not practical with the number of cells you're
trying to calculate (and also array entered):

=MAX(IF(COUNTIF(A1:A12000,"larry")1,LARGE((A1:A12 000="larry")*ROW(A1:A12000),ROW(INDIRECT("1:"&COUN TIF(A1:A12000,"larry")-1)))-LARGE((A1:A12000="larry")*(ROW(A1:A12000)),ROW(IND IRECT("2:"&COUNTIF(A1:A12000,"larry"))))-1),IF(A1<"larry",MATCH("larry",A1:A12000,0)-1),IF(A12000<"larry",ROWS(A1:A12000)+ROW(A1)-1-MAX(IF(A1:A12000="larry",ROW(A1:A12000),""))))


Change the range reference as needed. It takes a little bit to calculate
(but still under a minute on my machine). Without using any helper columns
or VBA- you almost have to use an array formula, which will be quite a
calculation load. If you have to do all 800 names, I'd look at using a
custom 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

[Vasilis Tergen]

To all parties having participated in this inquiry:

The formula I've been looking for may have not been conceived as
an interpolation of traditional functions.
However, a UDF, similar to the one kindly provided by JMB works.

Insofar as applying this function to my target, statistical report however,
a more interesting conclusion was drawn:

Using arguably one of the most powerful pc's available to the public,
200 cells containing complex formulas such as Mr. Coderres' or JMBs',
managed to halt excel repeatedly and ultimately, permanently.

The report I've created, presents statistical & other information,
regarding an average of 800, ever-changing names.
The report draws information from a separate worksheet,
which contains 12.000 rows and 22 columns of data.
More rows are added daily, averaging 12.000 additional rows/ year.
12000 rows* 22 columns= 264.000 cells.

The resulting report worksheet, contains 800 rows
(one for each name) and 20 columns.
Each of these cells, except for the names, contain mostly complex
array formulas. Thus, resulting in a total of 800*22= 17.600 cells.
I have found that if more than 10 rows of the report are filled with
formulas, (200 cells), excel cannot function.

Thus, regrettably, I now realize how the initial question was doomed from
the start.

Thank you very much for your cooperation.



"Vasilis Tergen" wrote:

Sir,

Your reply is greatly appreciated.

I invoked the formula you provided repeatedly.
Unfortunately, its' calculation result is not the desired one.
A) In a blank worksheet, containing only column A,
which in turn, contains "Larry" & other, various names, totalling 29
rows,
it results in a "11971" count.
B) In the target workbook, the results are of even greater disparity.
C) I can't be entirely certain as of the error in calculation, as the
formula in
question repeats COUNTIF commands, using discretionary (& or volatile)
references.

P.S. I am sorry I couldn't reply sooner. However, I had (& have) to
reply to all others which graciously offered a reply to this
post, in the order
that they did. (Including thoroughly testing their replies
respectively)


"JMB" wrote:

Another formula solution - but not practical with the number of cells you're
trying to calculate (and also array entered):

=MAX(IF(COUNTIF(A1:A12000,"larry")1,LARGE((A1:A12 000="larry")*ROW(A1:A12000),ROW(INDIRECT("1:"&COUN TIF(A1:A12000,"larry")-1)))-LARGE((A1:A12000="larry")*(ROW(A1:A12000)),ROW(IND IRECT("2:"&COUNTIF(A1:A12000,"larry"))))-1),IF(A1<"larry",MATCH("larry",A1:A12000,0)-1),IF(A12000<"larry",ROWS(A1:A12000)+ROW(A1)-1-MAX(IF(A1:A12000="larry",ROW(A1:A12000),""))))


Change the range reference as needed. It takes a little bit to calculate
(but still under a minute on my machine). Without using any helper columns
or VBA- you almost have to use an array formula, which will be quite a
calculation load. If you have to do all 800 names, I'd look at using a
custom 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

[JMB]

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

[Vasilis Tergen]

Sir,

Admittedly, I initially hadn't expected to need a UDF.
This, however, is the smartest approach simply because any
alternative (traditional- funtion) method, would result in an
augmented formula, which would in turn cause even the most
powerful machines to halt. (When updating the 12000 rows)

I effected your UDF.
Unfortunately, there seems to be a syntax error.
(Probably related to the first statement line)
I regrettably must also ask you to consider altering the syntax
altogether, as your (the previous) reply, reffered to my initial and
incomplete question.
The complete question can be found under my reply to Mr. Coderres'
2nd reply.
Lastly, I must clarify that I am not knowledgeable enough to alter or
correct your UDF myself.

I cannot but be both overwhelmed and hopeful by and of a
further, forward-pressing response.




"JMB" wrote:

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

[JMB]

Got back pretty late from a retirement party for one of my co-workers.

I'll try to take a look at it again tomorrow (if the problem is still
unresolved).


"Vasilis Tergen" wrote:

Sir,

Admittedly, I initially hadn't expected to need a UDF.
This, however, is the smartest approach simply because any
alternative (traditional- funtion) method, would result in an
augmented formula, which would in turn cause even the most
powerful machines to halt. (When updating the 12000 rows)

I effected your UDF.
Unfortunately, there seems to be a syntax error.
(Probably related to the first statement line)
I regrettably must also ask you to consider altering the syntax
altogether, as your (the previous) reply, reffered to my initial and
incomplete question.
The complete question can be found under my reply to Mr. Coderres'
2nd reply.
Lastly, I must clarify that I am not knowledgeable enough to alter or
correct your UDF myself.

I cannot but be both overwhelmed and hopeful by and of a
further, forward-pressing response.




"JMB" wrote:

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