Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array VLOOKUPs take 6 times longer than worksheet range VLOOKUPs
I can post the code but I am running compartive performance tests.
I read 10k rows that split to output 22k rows. During this I do 100k VLOOKUPS. Posting output directly to worksheet took 12 seconds. Creating an array and then moving all 22k records to a worksheet range took 10 second (20% reduction in processing time). So far so good. I moved the lookup table off the worksheet into an array (60 rows * 8 columns). Processing time INCREASED to 60 seconds (600% increase!) I must be doing something wrong but before I go nuts tweaking the code I would like to know if this surprises you or, are some functions (like VLOOKUPS) inherently slower when used in arrays? I didn't name the array as a table (couldn't figure out how to do that but the VLOOKUPs functioned by referencing "Array_Dates" without fail - just slow. Any comments? I can post the code but thought the general concept would be okay. Thanks, KG |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array VLOOKUPs take 6 times longer than worksheet range VLOOKUPs
Post the code, or the relevant bits of it.
"KGOldWolf" wrote: I can post the code but I am running compartive performance tests. I read 10k rows that split to output 22k rows. During this I do 100k VLOOKUPS. Posting output directly to worksheet took 12 seconds. Creating an array and then moving all 22k records to a worksheet range took 10 second (20% reduction in processing time). So far so good. I moved the lookup table off the worksheet into an array (60 rows * 8 columns). Processing time INCREASED to 60 seconds (600% increase!) I must be doing something wrong but before I go nuts tweaking the code I would like to know if this surprises you or, are some functions (like VLOOKUPS) inherently slower when used in arrays? I didn't name the array as a table (couldn't figure out how to do that but the VLOOKUPs functioned by referencing "Array_Dates" without fail - just slow. Any comments? I can post the code but thought the general concept would be okay. Thanks, KG |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array VLOOKUPs take 6 times longer than worksheet range VLOOKU
Sam,
When the "Array_Dates" was a table on the spreadsheet, the procedure took 10 seconds, switching to and array caused a huge increase. This must be happening because I have not named "Array_Dates" as a table - I think the lookup is somehow examining every cell in the array until it finds a hit (it will always find a hit). I know there must be a way to name and set an array as a table - I just haven't found it yet. Thanks, Ken Print_Line_Array(Output_Row_Number, 8) = Application.WorksheetFunction.VLookup(Lookup_Date, Array_Dates, 2, True) ' Days In Month "Sam Wilson" wrote: Post the code, or the relevant bits of it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Function to do a Sum of VLookUps that translate a letter gra | Excel Discussion (Misc queries) | |||
Dependent vlookups - nested vlookups (maybe) | Excel Worksheet Functions | |||
Array Vlookups | Excel Programming | |||
Vlookups array function by Alan Beban | Excel Worksheet Functions | |||
Vlookups array function by Alan Beban | Excel Worksheet Functions |