I need a better formula.

I have two lists (that change frequently) one is up to 50 items long, and
the other is up to 5000 items long. Both are in columns. Many of the items on
the 5000-item list are on the 50-item list, but not all of them. I need to
know the arrary position of the last item on the 5000-item list that is a
member of the 50-item list. So if there are currently 750 items on the long
list, but the last 9 are not on the short list, I need the formula to return
741.

The way I'm doing it now works, but causes, "Not enough system resources to
display completely" and that's causing problems. Currently, I'm using a
helper column that is 5000 rows long. All it does is look at the
corresponding spot on the long list and outputs row() if that spot is on the
short list (I'm using SUMPRODUCT) otherwise 0, then I look at the MAX() of
the helper column.

I appreciate your ideas!
Thanks,

Try using this formula, against each of the values in your 5000-item list:
=IF(ISERROR(MATCH(A3,$E$1:$E$50,0)=TRUE),"",ROW(A3 ))

$E$1:$E$50 is your 50-item target list. In an empty cell at the bottom of
the column containing the above formula, insert the formula: =max(b1:b5000)
and it will return the row number containing the last item in the 5000 list
that matches your 50-item target list.

"sb1920alk" wrote:

I need a better formula.

I have two lists (that change frequently) one is up to 50 items long, and
the other is up to 5000 items long. Both are in columns. Many of the items on
the 5000-item list are on the 50-item list, but not all of them. I need to
know the arrary position of the last item on the 5000-item list that is a
member of the 50-item list. So if there are currently 750 items on the long
list, but the last 9 are not on the short list, I need the formula to return
741.

The way I'm doing it now works, but causes, "Not enough system resources to
display completely" and that's causing problems. Currently, I'm using a
helper column that is 5000 rows long. All it does is look at the
corresponding spot on the long list and outputs row() if that spot is on the
short list (I'm using SUMPRODUCT) otherwise 0, then I look at the MAX() of
the helper column.

I appreciate your ideas!
Thanks,

This is close. I should have mentioned before, the reason I'm using
SUMPRODUCT is because I'm considering more than just if the particular item
on the long list is on the short list. The short list are call agents. The
long list is a call log that our system produces. The log includes all calls.
I'm not interested in all calls, only calls made by a call agent. SUMPRODUCT
cancels out calls with 0 second durations, internal calls (neither inbound or
outbound), and for the inbound calls it cancels out anything that goes
straight to voice mail. So I need it to consider all of these same conditions.

"tman" wrote:

Try using this formula, against each of the values in your 5000-item list:
=IF(ISERROR(MATCH(A3,$E$1:$E$50,0)=TRUE),"",ROW(A3 ))

$E$1:$E$50 is your 50-item target list. In an empty cell at the bottom of
the column containing the above formula, insert the formula: =max(b1:b5000)
and it will return the row number containing the last item in the 5000 list
that matches your 50-item target list.

"sb1920alk" wrote:

I need a better formula.

I have two lists (that change frequently) one is up to 50 items long, and
the other is up to 5000 items long. Both are in columns. Many of the items on
the 5000-item list are on the 50-item list, but not all of them. I need to
know the arrary position of the last item on the 5000-item list that is a
member of the 50-item list. So if there are currently 750 items on the long
list, but the last 9 are not on the short list, I need the formula to return
741.

The way I'm doing it now works, but causes, "Not enough system resources to
display completely" and that's causing problems. Currently, I'm using a
helper column that is 5000 rows long. All it does is look at the
corresponding spot on the long list and outputs row() if that spot is on the
short list (I'm using SUMPRODUCT) otherwise 0, then I look at the MAX() of
the helper column.

I appreciate your ideas!
Thanks,

Might help if you posted up a sample set of data - anonymize names if you
need to (truncate last names off, or use find/replace).

"sb1920alk" wrote:

This is close. I should have mentioned before, the reason I'm using
SUMPRODUCT is because I'm considering more than just if the particular item
on the long list is on the short list. The short list are call agents. The
long list is a call log that our system produces. The log includes all calls.
I'm not interested in all calls, only calls made by a call agent. SUMPRODUCT
cancels out calls with 0 second durations, internal calls (neither inbound or
outbound), and for the inbound calls it cancels out anything that goes
straight to voice mail. So I need it to consider all of these same conditions.

"tman" wrote:

Try using this formula, against each of the values in your 5000-item list:
=IF(ISERROR(MATCH(A3,$E$1:$E$50,0)=TRUE),"",ROW(A3 ))

$E$1:$E$50 is your 50-item target list. In an empty cell at the bottom of
the column containing the above formula, insert the formula: =max(b1:b5000)
and it will return the row number containing the last item in the 5000 list
that matches your 50-item target list.

"sb1920alk" wrote:

I need a better formula.

I have two lists (that change frequently) one is up to 50 items long, and
the other is up to 5000 items long. Both are in columns. Many of the items on
the 5000-item list are on the 50-item list, but not all of them. I need to
know the arrary position of the last item on the 5000-item list that is a
member of the 50-item list. So if there are currently 750 items on the long
list, but the last 9 are not on the short list, I need the formula to return
741.

The way I'm doing it now works, but causes, "Not enough system resources to
display completely" and that's causing problems. Currently, I'm using a
helper column that is 5000 rows long. All it does is look at the
corresponding spot on the long list and outputs row() if that spot is on the
short list (I'm using SUMPRODUCT) otherwise 0, then I look at the MAX() of
the helper column.

I appreciate your ideas!
Thanks,

Ok, I cut it down to only 1000 calls and 30 agents, I eliminated the formula
in question including the helper column and replaced it with NOW(), and I'm
still getting "Not enough system resources to display completely" so this
isn't going to fix my real issue. I started getting this error after I added
an macro that triggers the refresh every X seconds. The procedure hangs when
the error dialog appears and finishes correctly when I push OK. I want to
have it run on it's own and not have to push anything. Part of the marco
saves a copy as a web page onto our intranet site so all of the call agents
can see their call numbers and times. Everything works correctly except for
the "Not enough system resources to display completely" and even that works
correctly if someone pushes OK every time it comes up. I originally thought
that the "Not enough system resources to display completely" error was due to
the large number of calculations in the helper column, but I guess that's not
it. I'll have to take a closer look at the macros. Anyways, I appreciate your
help. By the way, do you know of any way to disable this error notification?
I've try to free programs that claim to look for OK buttons and click them
automatically, but neither of them have worked for me in my tests.

Regards,

"tman" wrote:

Might help if you posted up a sample set of data - anonymize names if you
need to (truncate last names off, or use find/replace).

"sb1920alk" wrote:

This is close. I should have mentioned before, the reason I'm using
SUMPRODUCT is because I'm considering more than just if the particular item
on the long list is on the short list. The short list are call agents. The
long list is a call log that our system produces. The log includes all calls.
I'm not interested in all calls, only calls made by a call agent. SUMPRODUCT
cancels out calls with 0 second durations, internal calls (neither inbound or
outbound), and for the inbound calls it cancels out anything that goes
straight to voice mail. So I need it to consider all of these same conditions.

"tman" wrote:

Try using this formula, against each of the values in your 5000-item list:
=IF(ISERROR(MATCH(A3,$E$1:$E$50,0)=TRUE),"",ROW(A3 ))

$E$1:$E$50 is your 50-item target list. In an empty cell at the bottom of
the column containing the above formula, insert the formula: =max(b1:b5000)
and it will return the row number containing the last item in the 5000 list
that matches your 50-item target list.

"sb1920alk" wrote:

I need a better formula.

I have two lists (that change frequently) one is up to 50 items long, and
the other is up to 5000 items long. Both are in columns. Many of the items on
the 5000-item list are on the 50-item list, but not all of them. I need to
know the arrary position of the last item on the 5000-item list that is a
member of the 50-item list. So if there are currently 750 items on the long
list, but the last 9 are not on the short list, I need the formula to return
741.

The way I'm doing it now works, but causes, "Not enough system resources to
display completely" and that's causing problems. Currently, I'm using a
helper column that is 5000 rows long. All it does is look at the
corresponding spot on the long list and outputs row() if that spot is on the
short list (I'm using SUMPRODUCT) otherwise 0, then I look at the MAX() of
the helper column.

I appreciate your ideas!
Thanks,

Well, it's been 40 minutes and it's still working. Think the problem was that
the computer was locked. I usually access it with remote desktop connection.
This time I walked over to it, disabled the screen saver and left it
unlocked. So far, so good. Do you think there's a relationship between the
"Not enough system resources to display completely" error and having the
computer locked vs unlocked?

"sb1920alk" wrote:

Ok, I cut it down to only 1000 calls and 30 agents, I eliminated the formula
in question including the helper column and replaced it with NOW(), and I'm
still getting "Not enough system resources to display completely" so this
isn't going to fix my real issue. I started getting this error after I added
an macro that triggers the refresh every X seconds. The procedure hangs when
the error dialog appears and finishes correctly when I push OK. I want to
have it run on it's own and not have to push anything. Part of the marco
saves a copy as a web page onto our intranet site so all of the call agents
can see their call numbers and times. Everything works correctly except for
the "Not enough system resources to display completely" and even that works
correctly if someone pushes OK every time it comes up. I originally thought
that the "Not enough system resources to display completely" error was due to
the large number of calculations in the helper column, but I guess that's not
it. I'll have to take a closer look at the macros. Anyways, I appreciate your
help. By the way, do you know of any way to disable this error notification?
I've try to free programs that claim to look for OK buttons and click them
automatically, but neither of them have worked for me in my tests.

Regards,

"tman" wrote:

Might help if you posted up a sample set of data - anonymize names if you
need to (truncate last names off, or use find/replace).

"sb1920alk" wrote:

This is close. I should have mentioned before, the reason I'm using
SUMPRODUCT is because I'm considering more than just if the particular item
on the long list is on the short list. The short list are call agents. The
long list is a call log that our system produces. The log includes all calls.
I'm not interested in all calls, only calls made by a call agent. SUMPRODUCT
cancels out calls with 0 second durations, internal calls (neither inbound or
outbound), and for the inbound calls it cancels out anything that goes
straight to voice mail. So I need it to consider all of these same conditions.

"tman" wrote:

Try using this formula, against each of the values in your 5000-item list:
=IF(ISERROR(MATCH(A3,$E$1:$E$50,0)=TRUE),"",ROW(A3 ))

$E$1:$E$50 is your 50-item target list. In an empty cell at the bottom of
the column containing the above formula, insert the formula: =max(b1:b5000)
and it will return the row number containing the last item in the 5000 list
that matches your 50-item target list.

"sb1920alk" wrote:

I need a better formula.

I have two lists (that change frequently) one is up to 50 items long, and
the other is up to 5000 items long. Both are in columns. Many of the items on
the 5000-item list are on the 50-item list, but not all of them. I need to
know the arrary position of the last item on the 5000-item list that is a
member of the 50-item list. So if there are currently 750 items on the long
list, but the last 9 are not on the short list, I need the formula to return
741.

The way I'm doing it now works, but causes, "Not enough system resources to
display completely" and that's causing problems. Currently, I'm using a
helper column that is 5000 rows long. All it does is look at the
corresponding spot on the long list and outputs row() if that spot is on the
short list (I'm using SUMPRODUCT) otherwise 0, then I look at the MAX() of
the helper column.

I appreciate your ideas!
Thanks,