Help: Macro to Combine Match Data From Two Sheets

[Mike951]

I have a workbook with two sheets, AB1matches and Sheet2. AB1matches has about 130,000 rows, while Sheet2 has about 800. I want to be able to take the first column of the AB1matches, and find a corresponding match from the first column of Sheet2. Every item in the first column of Sheet2 can be found in first column of AB1matches. I want to output the result to Sheet3, including all 14 columns of associated data from AB1matches(item number, product name, description, vendor, etc.) with the corresponding data from the match on Sheet2 from that match's row ( 7 columns total). This code is not throwing an error, however, it is just doing nothing, please help!:

Please Login or Register  to view this content.

Thanks
Mike

[Andrew-R]

Hello, and welcome to the forum. Nasty stuff first, can you please read the forum rules and use code tags when you post, please?

Next, your problem - the reason your code does nothing is because you've told it to. If every value on sheet2 can be found in AB1Matches then you'll always exit the V2 loop with intRepeated set to 1, but your code that writes the data starts off with:

Please Login or Register  to view this content.

Change the "=" to a "<>" and you'll be there. Which will lead you straight to your next problem ... this thing will take forever to run, because looking for values cell-by-cell is very, very slow. Would you be interested in a faster way to do this?

[Mike951]

would you be interested in a faster way to do this?

yes please!

[Andrew-R]

OK, the fastest way is to loop through the smaller list (Sheet2) and then use Excel's .Find method to find the value in the big list, something like this:

Please Login or Register  to view this content.

Should do the job, but I've typed it up in this message, rather than testing it in Excel, so there might be a couple of little errors

[Andrew-R]

Sorry, just realised that there was no need for the write loop:

Please Login or Register  to view this content.

Should be quicker.