abraxus
09-17-2013, 08:39 PM
bare with me here....
i am using excel 2012, and i have one worksheet that has 4000 records to process, and for each of those 4000 records, i have to look up a value from another worksheet that has over 600,000 rows just to get a value, and for each row in the first, i have to do this about 10 times (dont ask, it's for corporate data processing)
currently they are using formulas and VLOOKUP (5 times for each row in the first) - and the process takes about 40 minutes to process - i converted it to an in memory array in VBA thinking that would speed things up, but it didnt speed it up all that much
i am doing this in VBA, and i am trying to come up with something that speed up the process considerably, and i am thinking that if i could dynamically create an indexed database table (like using access object library?) and then using some select statements or table searches based on the index within the loop instead of in memory arrays to see if might be faster
got everything i try to look up assumes that i'm trying to connect excel to an existing database... that's not what i want to do - i just want to create an "in memory" temporary look up table that has an index which i can use to get the associated value i need from the 600,000 record table quicker than scanning manually thru the in memory array
am i just dreaming that this might be possible? or is the size of these two worksheet, simply too large to process any faster?
i know how to write vba code pretty well, and i'm familiar with database schemas and stuff, and if i was within access or connected to an existing database, this would be simple - i just want to to use vba excel to create an indexed temporary table, thinking that it might be faster to process within the 4000 record loop
just some sample code would be great is any of you have any ideas
google doesnt help at all
i am using excel 2012, and i have one worksheet that has 4000 records to process, and for each of those 4000 records, i have to look up a value from another worksheet that has over 600,000 rows just to get a value, and for each row in the first, i have to do this about 10 times (dont ask, it's for corporate data processing)
currently they are using formulas and VLOOKUP (5 times for each row in the first) - and the process takes about 40 minutes to process - i converted it to an in memory array in VBA thinking that would speed things up, but it didnt speed it up all that much
i am doing this in VBA, and i am trying to come up with something that speed up the process considerably, and i am thinking that if i could dynamically create an indexed database table (like using access object library?) and then using some select statements or table searches based on the index within the loop instead of in memory arrays to see if might be faster
got everything i try to look up assumes that i'm trying to connect excel to an existing database... that's not what i want to do - i just want to create an "in memory" temporary look up table that has an index which i can use to get the associated value i need from the 600,000 record table quicker than scanning manually thru the in memory array
am i just dreaming that this might be possible? or is the size of these two worksheet, simply too large to process any faster?
i know how to write vba code pretty well, and i'm familiar with database schemas and stuff, and if i was within access or connected to an existing database, this would be simple - i just want to to use vba excel to create an indexed temporary table, thinking that it might be faster to process within the 4000 record loop
just some sample code would be great is any of you have any ideas
google doesnt help at all