Alternative to Xlookup loops for large dataset in a macro?

[scampling]

Hi all. I'm pretty new to VBA and macros and have been muddling through, but have arrived at a problem I can't solve and I'd really appreciate any advice or help that can be given :-)

I've got a Sheet with 70,000 customer transactions, and another Sheet in the same Workbook with details about 20,000 customers. I'm trying to add so code to a macro that will go through each of the 70,000 transactions and pull in some info from the relevant line out of the 20,000 customers. I managed to do this with some loops when the data set was MUCH smaller, but using the same process now seems to be making Excel crash - either that or I've not been patient enough to leave it running long enough. Either way, it needs some improvement!!

A much smaller version of the file is attached to this post, confidential data removed.

The way my loop was working was to compare the "Customer Number" in the "Distributor Sales" sheet with the "Customer Number" in the "Customer Countries" sheet, and pull out the "Country", "Customer Name" and "Type" back into the "Distributor Sales" sheet. I'd been using these loops:

Please Login or Register  to view this content.

Is there a better way to do this without the repeating loops? Perhaps by making the reference data in the "Customer Countries" into an array, or a scripting dictionary? Thanks in advance!!

[Richard Buttrey]

Hi, and welcome to the forum

Never use VBA if there is standard excel functionality that can achieve what you want.
VBA is much slower than standard Excel and particularly where loops that refer back to Excel at each pass through the loop are used since there's a time overhead for VBA to jump to Excel and back to VBA.

In your case in T2 on the Distributor Sales sheet enter

Please Login or Register  to view this content.

and copy it down. The '4' means the 4th column so for other stuff like Country and Name change to 2 & 3

If this is something you do regularly then write a macro which will add the formulae for you.

[scampling]

Richard - that's amazing, thank you SO MUCH! Sorted everything :-D