Forgot the absolutes for the list ranges:
=IF(ISNA(MATCH(B47063,$B$2:$B$47052,0)),"x",INDEX($C$2:$C$47052,MATCH(B47063
,$B$2:$B$47052,0)))
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
The formula you posted is a classic example of a double Vlookup which bloats
the calc times of *large* WSs.
I would appreciate you trying the following formula, and posting back with
the calc times, as compared to your original one.
There has to be a definite improvement.
=IF(ISNA(MATCH(B47063,B2:B47052,0)),"x",INDEX(C2:C47052,MATCH(B47063,B2:B470
52,0)))
A similar formula that I revised in my large WS saved almost 50% in calc
time, where a triple Vlookup, as I said before, saved almost 75%.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
Thanks Don,
I have to keep it relatively simple,
I have a partner in Bangkok who would baulk
at using Macro's. (That's if it would work in our case)
RD, thanks for your reply,
I have "worked around" by using 5 templates
each using 30,000 or so in the array
and using this formula.........
=IF(ISERROR(VLOOKUP(B47063,$B$2:$C$47052,2,FALSE)),"x",(VLOOKUP(B47063,$B$2:
$C$47052,2,FALSE)))
The seek time is about 30 seconds for each,
however when I had 5 rows (arrays) the resultant time was 12 minutes
Regards
--
(e-mail address removed)
(Remove gum to reply)
| Vlookups are notoriously slow, especially if you're error trapping using
| double and/or triple Vlookup formulas.
|
| I have a few old, very large data base type WSs which *had* exhibited calc
| times similar to what you are seeing.
|
| I've since revised them, based on information shared within these NGs, and
| have been able to reduce the calc times, in some cases, to a better then
75%
| improvement.
|
| The main improvement was realized when changing the formulas from Vlookup
to
| Index and Match, and eliminating the double Vlookups for error trapping.
|
| I would advise you to experiment with revising your formula approach, and
| see if a function change can help you.
|
| If you wish, post some of your largest (long, complex) Vlookup formulas
| here, and lets see if we can help with suggesting shorter calc time
| alternatives.
| --
|
| HTH,
|
| RD
| ==============================================
| Please keep all correspondence within the Group, so all may benefit!
| ==============================================
|
| | Thanks Max,
| we use MSWorks for the majority of our work
| and the simple program moves along at light speed
| so we get a bit spoilt
|
|
|
| --
| (e-mail address removed)
| (Remove gum to reply)
|
|
| | | FWIW, yes.
| |
| | I'm also in xl97 and have come across the same Excel
| | behaviour you posted, whenever there's a terrific amount
| | of calcs involved (with a PIII-500 with 128Mb RAM)
| |
| | Most important is that correct, calculated results
| | are returned at the end of it.
| |
| | --
| | Rgds
| | Max
| | xl 97
| | ---
| | Please respond, in newsgroup
| | xdemechanik <at>yahoo<dot>com
| | ----
| |
| | "Rodney" wrote:
| | >During a VLookup,
| | >(A 1400 name list, looks for matches in a 150,000 array)
| | >
| | >The seek time takes just under 12 minutes,
| | >there is no "hourglass" indicating the program is
| | computing
| | >The "calculating time" stays on 0% until the operation
| | >is about over then quickly spins through the percentage
| | table
| | >to finish.
| | >Is this roughly a correct scenario?
| | >
| | >I have a rather flaccid 386 celeron with ASUS MB
| | >and 300MB ram.
| | >
| | >Thx
| | >
| | >
| | >--
| | >
[email protected]
| | >(Remove gum to reply
|
|
|
