VBA and researching data thru 20.000 records

M

Maileen

Hi,

I have 2 sheets with several fields.
on sheet1, i have new data and on sheet2 i have old data.

i would like to update 2 fields of sheet1 with data from sheet2.

for that i want to compare 3 fields, let's say C, D, E.

if C, D, E are equal on sheet1 and sheet2, so i want to copy data from
fields A and B (from sheet2) to fields A and B of sheet1.

for now i use selection.autofilter .....
on the 3 fields to compare, but with my 20,000 records, it's really slow
(on 3.1 Ghz CPU and 1 Ghz ram).

So i would like to know if exists another way how to do it and for sure
faster ?
thanka a lot for help.

maileen
 
N

Niek Otten

Hi Maileen,

You can include an extra column in Sheet 2, with C,D and E concatenated.
Then from Sheet1 you can do a VLOOKUP with concatenated keys.
If the keys are numbers, make sure they occupy the same number of characters
using the TEXT() function, otherwise there is the risk of unintended
matching.

For long lists, it is best to make sure they are sorted. Then you can use
TRUE as the third argument of the VLOOKUP (or omit it) which makes it an
order of magnitude faster. But then you'll have to check yourself if the key
found matches the one searched exactly. That requires a formula like

=IF(VLOOKUP(A1,E1:F16000,1)=A1,VLOOKUP(A1,E1:F16000,4),NA())

Note the different column numbers in the VLOOKUPs

Anyway, even if you do not sort, you'll probably find that Excel's built-in
functions are much faster than anything you write yourself.
 
N

Niek Otten

Of course the (faked) column number can not be 4 in E1:F16000, but 2 max!

Hope you got the idea, however.
 
Top