VB Lookup Required
Posted by Malc on August 02, 2001 3:12 AM
I have a data table and a lookup table. The data table is some 40,000 rows the lookup table is 3500 rows. I've used Vlookup to get data from the lookup to data table. (Getting cost centre names for cost centre codes in a trial balance) . Excel takes along time to do the calculation So I wrote a bit of code that loads the lookup table into a two dimension array (code, name) and a loop that gets the cost centre code one cell at a time from the data table then loops through all 3500 code entries in the array, until it finds a match, when it does it unloads the array value in the cell next to the cost centre code. Cost Centres codes are repeated many times in the data table on various rows and because it's big and has many other formula a sort is really slow.
The array works fairly quickly but must be very inefficent having to scroll through all 3500 entries for all 40,000 rows. There must be a better and faster way. Any ideas?
Posted by Aladin Akyurek on August 02, 2001 3:20 AM
A request
Malc -- Just curious about the VLOOKUP formula that takes so much time to execute. Would je post that?
Aladin
Posted by Malc on August 02, 2001 3:26 AM
Re: A request
Posted by Malc on August 02, 2001 3:29 AM
Re: A request
Just a thought that is only one of quite a few formulas in the spreadsheet some are dependant on the lookups return which also have to be recalculated if something in the lookup table changes.
Posted by Aladin Akyurek on August 02, 2001 3:33 AM
Re: A request
Are cost centre codes alphanumeric? If they are, did you consider sorting the lookup table and omitting the false arg from the formula?
Posted by Aladin Akyurek on August 02, 2001 3:37 AM
Re: A request
Are you saying that the lookup table is subject to updates (that is, deletions & additions?
Posted by Malc on August 02, 2001 3:44 AM
Re: A request
Yep the trial balance is fresh every time as is the lookup table. Both emerge from the ledger in a fixed format. So it's download, paste into a template with all the formula update lookup ranges and have smoko. We also do some sensitivity analysis so the odd tweak in the lookup occurs. The idea of the macro was to speed up the lookup part which seems to take most of the time and it does speed it up. Still it could go faster.
Posted by Malc on August 03, 2001 11:59 AM
Solved It
So I wrote a bit of code that loads the lookup table into a two dimension array (code, name) and a loop that gets the cost centre code one cell at a time from the data table then loops through all 3500 code entries in the array, until it finds a match, when it does it unloads the array value in the cell next to the cost centre code. Cost Centres codes are repeated many times in the data table on various rows and because it's big and has many other formula a sort is really slow.
I changed to single demension arrays and are using a type of bubble sort, (reducing halves) to find the matching data in the array and also have sorted the data table and lookup data into cost centre order. The VBA lookup looks up four colums of data (equiv to four vlookups) and takes three minuted to post the lookup. Forumula vlookups takes over 10.
Posted by Aladin Akyurek on August 03, 2001 1:39 PM
Re: Solved It
Great. I thought I blocked possible replies by VBA'ers, so was regretting my request. By the way, interesting performance comparison!
Aladin
