Thanks to both of you for the guidance. I ended up using the method
suggested by Pete. It was exactly what I was looking for. Lots of
good learnings packed in that solution. No issue with speed.




Pete_UK wrote:
Thanks, RD - so what I'd read about MATCH being quick (I think it was
on the Decision Models site) seems to be true !!

I think this slight variation in the formulae should be quicker still:

B1: =CHAR(MATCH(A1,Sheet1!A$1:D$1)+64)
C1: =IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&B1&":"&B1),0) ),FALSE*,TRUE)

Hopefully the OP will be able to try it out and report back.

Hope this helps.

Pete

RagDyer wrote:
In duplicating the exact OP conditions, both formulas take approximately the
same time to calculate IF a single value is changed in the 1000 row list ...
BUT ... to build the formulas in the adjoining column ... there was a
gigantic time difference.

Your 2 columns of formulas filled down in approx. 3 or 4 seconds.
The single Sumproduct column took in excess of *5 minutes*.

When changing a block of values (25) in the 1000 row list, your formulas
took maybe 2 seconds, while the Sumproduct took about 15 seconds.

I would say that your suggested formulas are definitely the better way to
go.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pete_UK" wrote in message
oups.com...
I've just re-read what the OP has - 1000 values in another sheet, so
assume the data is on Sheet1 and the 1000 values in A1 to A1000, with
these formula in B1 and C1:

B1: =MATCH(A1,Sheet1!A$1:D$1)
C1:
=IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&CHAR(B1+64)&" :"&CHAR(B1+64)),0)),FALSE,TRUE)

Then copy these down to row 1000 - it takes about 4 - 5 seconds to
recalculate.

Hope this helps - he's probably gone to bed by now !! <bg

Pete

Pete_UK wrote:
I put 1 in A1 and then incremented this by 3 down columns A to D to
simulate what I think the OP has (or should have).

F1 is for the number to be found, with this formula in G1:

=MATCH(F1,A1:D1)

and this formula in H1:

=MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1+64)),0)

This returns #N/A if the number in F1 is not found (easily trapped with
IF(ISNA( ... etc) and the row number that the number is on if the
sought-number is present. The response is instant (well I can't detect
any delay after entering a new number in F1).

I think the OP wanted a True or False result, so change the formula in
H1 to:

=IF(ISNA(MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1 +64)),0)),FALSE,TRUE)

and hide column G.

Hope this helps.

Pete

RagDyer wrote:
If you could eliminate a *single* row, the Sumproduct formula would do
the
trick:

=SUMPRODUCT(--(Sheet2!$A$2:$D$65536=A1))<0

This calculates in *less* then 4 seconds!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.