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?