Search for SKU, if match then copy description from 1 Workbook in another

[tripey]

Hey guys, I'm working with two workbooks. Book1 needs to search Book2 for a matching sku. If a match is found, then copy description in book2 over to book1. I had this working with VLOOKUPS but unfortunately I need it to leave the description alone if no matching sku is found.. so VLOOKUP is not ideal for this situation. I'm still new to all of this and was hoping somebody could help me with a macro. I've attached the sample files. Thanks!

[Special-K]

Try this

Please Login or Register  to view this content.

[tripey]

Awesome, checking it out now. Thanks

[tripey]

If you're around reading this... I neglected to mention I'm working with about 41000 items in book1 and 70000 in book2. The macro would take a while to run right?

[xladept]

Hi tripey,

I tweaked special k's code for a big file:

Please Login or Register  to view this content.

[tripey]

Right on! Thanks, I let k's macro run for about 40 min and it was still running. I just adapted your macro to my workbook names and column numbers. What do you think a normal run time for my situation? I just started it.

Thanks

[cytop]

Also posted: http://www.ozgrid.com/forum/showthread.php?t=178945

[xladept]

Inside of 5 minutes, maybe just about a minute??

[tripey]

Oh wow, it's running about 30 or so minutes for me. It works as far as I can tell so thank you very much. I'm gonna close the other thread in the other forum sorry about that.

[xladept]

I just adapted your macro to my workbook names and column numbers. What do you think a normal run time for my situation? I just started it.

It should run faster than that - I ran 64k rows just the other day in less than a minute - what kind of adaptations have you made??

[tripey]

Just changed book1 and book2 to the workbook names I'm using. Also the column numbers. That's all I changed. As soon as it's done running, I'll paste the code in here.

[tripey]

Here's the code

Please Login or Register  to view this content.

[xladept]

Hi Tripey,

I'll sort the second workbook and rewrite the code - but I can keep the second workbook in core and replace it when the macro has finished - unless it's already sorted??

[tripey]

Workbook2's skus are sorted. Workbook1's skus are not sorted. I'm not following your statement about keeping the second workbook in core. I really appreciate your help. I'm still running the macro right now as I have 3 more columns worth of values to copy over from matching the skus. Thanks

Hi Tripey,

I'll sort the second workbook and rewrite the code - but I can keep the second workbook in core and replace it when the macro has finished - unless it's already sorted??

[xladept]

Hi Tripey,

Since they're sorted this should speed up the process:

Please Login or Register  to view this content.

[tripey]

Oh yeah! After adding the comparison operator in there, the macro completes in a matter of seconds. Awesome! Thank you so much!

[xladept]

You're welcome! It doesn't need to iterate the whole 70k rows if a match is out of the question

[tripey]

Ugh, I spoke too soon. When I was first testing the macro with the comparison operator, I forgot that I already had a column populated... so after the macro was complete, I thought it worked. I'm testing it now and it runs quick but doesn't populate anything. It seems like it's jumping out of the For loop as soon as workbook1's sku is > than workbook2's.

So if workbook1's sku is > than workbook2's sku, it will GOTO GetNext. In GOTO GetNext, it'll goto next i, which is the next sku in workbook1.. what is UsedRange? is it some kind of counter?


No big deal for now, I'm done with the workbook, but I'd like to know why it's not populating the column for future reference.

[xladept]

Hi Tripey,

Too good to be true

Please Login or Register  to view this content.

[tripey]

I know right. I'm getting tired anyways, been messing with this all day. I ended up sorting workbook2 where the skus go from greatest to smallest. Then workbook1 from smallest to greatest. This seems to work as the macro won't jump out of the loop as it's comparing skus. I'm gonna double check a couple things before I pass out

[xladept]

Hi Tripey,

The code I fixed (post # 19) wants workbook2 to be sorted ascending - if I had just put a less than on the first post with the comparison then it should have cut the time by a half to two thirds - sorry for the mistake - I think that I was confused there at the end.