Hi,

I have a problem when using the offset function. I am using the Match
function to find the first and last occurences of a string in a series
of data. When the string is early in the data this works. When the
string is late in the data -- past 1000 rows -- the offset function
returns nothing.

Does anybody know why this is??

Here is the formula:

=COUNT(OFFSET(MGCSales,MATCH($F$5,Source!$A$3:$A$3 3412,0),8,MATCH($F
$5,Source!$A$3:$A$33412,1)-MATCH($F$5,Source!$A$3:$A$33412,0),1))

Thanks in advance for your help.

A

Venturing some thoughts ..

Think the prob lies in this term:
MATCH($F$5,source!$A$3:$A$33412,1)
which may not always return the "last" row

Think we could try replacing the above with this expression (it requires
array-entry):
MATCH(MAX(IF($F$5=source!$A$3:$A$33412,ROW($A$3:$A $33412))),IF($F$5=source!$A$3:$A$33412,ROW($A$3:$A $33412)),0)

Try, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula:
=COUNT(OFFSET(MGCSales,MATCH($F$5,source!$A$3:$A$3 3412,0),8,MATCH(MAX(IF($F$5=source!$A$3:$A$33412,R OW($A$3:$A$33412))),IF($F$5=source!$A$3:$A$33412,R OW($A$3:$A$33412)),0)-MATCH($F$5,source!$A$3:$A$33412,0),1))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"alan82" wrote in message
...
Hi,

I have a problem when using the offset function. I am using the Match
function to find the first and last occurences of a string in a series
of data. When the string is early in the data this works. When the
string is late in the data -- past 1000 rows -- the offset function
returns nothing.

Does anybody know why this is??

Here is the formula:

=COUNT(OFFSET(MGCSales,MATCH($F$5,Source!$A$3:$A$3 3412,0),8,MATCH($F
$5,Source!$A$3:$A$33412,1)-MATCH($F$5,Source!$A$3:$A$33412,0),1))

Thanks in advance for your help.

A

Alan,

No feedback for the thoughts ventured?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---

"Max" wrote...
No feedback for the thoughts ventured?

You need to learn how to read NNTP header tags. Had you done so, you
might have noticed that the OP posted a bit after 9 AM GMT from the
British time zone. You posted your first response around 4:40 AM (19
Nov) from Singapore's time zone, which would be around 8:40 PM (18
Nov) GMT. In the OP's local time, if the OP posted from work in the
morning then left work even as late as 8 PM (so 40 minutes BEFORE you
posted your 1st response), there would have been NOTHING to which the
OP could sensibly respond/for which to provide feedback.

You probably need to wait until 5 PM your time (9 AM GMT) for the OP
to have a chance to see your first response. Right now (as I write
this, 3 AM GMT 19 Nov) the OP is either asleep or has much, much
better things to do than check newsgroups.

Patience!

alan82 wrote...
I have a problem when using the offset function. I am using the Match
function to find the first and last occurences of a string in a series
of data. When the string is early in the data this works. When the
string is late in the data -- past 1000 rows -- the offset function
returns nothing.

Does anybody know why this is??
....
=COUNT(OFFSET(MGCSales,MATCH($F$5,Source!$A$3:$A$ 33412,0),8,
MATCH($F$5,Source!$A$3:$A$33412,1)-MATCH($F$5,Source!$A$3:$A$33412,0),1))

Is Source!$A$3:$A$33412 sorted in ascending order? If not, MATCH($F
$5,Source!$A$3:$A$33412,1) is unreliable. That's the most likely
reason your formula fails.

Also, your 2nd argument to your OFFSET call gives the row index
(starting from 1) in Source!$A$3:$A$33412 of the first instance of the
value of F5. Unless MGCSales has a header row as its top row that you
want to skip, you should be subtracting 1 from the first MATCH call's
result.

You could avoid the volatile OFFSET call and make the formula clearer
using two INDEX calls. Defining the name LIST referring to Source!$A
$3:$A$33412,

=COUNT(INDEX(MGCSales,MATCH($F$5,LIST,0),9):INDEX( MGCSales,MATCH(2,1/
(LIST=$F$5)),9))