VBA VlookUP on large data set

[nefdog]

I have two table, table1 has over 450 thousand rows that include loan number, error type, and responsible job role. table2 has responsible job roles and the department associated with each respective job role.

I'm looking for a fast performing vba code that would look at the responsible job role on table1 and return the matching department from table2, and the result should be written on table1 as a value, given that the whole reason that I want to do this is because I want to get away from having a table that has over 450k vlookups.

thanks in advance

 

[Jerry Sullivan]

Peter, That is better. On my PC for 450K rows runs in 2.7 seconds vs. 3.9. More importantly, it's simpler.

I considered forgoing the testing when populating the dictionary, and opted not to because I wanted to emulate Vlookup behavior of taking the first match. Since this is a lookup table, the constraint that you suggest of having no duplicates makes perfect sense.

Since the lookup is so small, the time savings are coming from using a results array instead of trying to get double-duty out of a single array.

Nicely done!

 

[L. Howard]

@ Jerry and Peter,

I love those 'BLINK' fast codes! Wish I had the where-with-all to write them.

I'll archive them with my Resize version code, knowing that all three do the same thing, but not understanding much of how two of them actually work.

Howard

 

[Diana Ego]

Hi,

I have used the first method but it still takes a lot of time on my computer, I don't understand why.
Can anyone help?

The next question would be: how can I adjsut it if the two tables are in dfiferent files?

Thank you in advance.