Compare Data between 2 Workbooks & Copy Add'l Data If Matched

[Marcieface]

You all were so helpful on my first question! Now I have a second question for you.

I have 2 workbooks, Master Template = w1, Allocated List = w2.

I have already created a Macro in w1 to copy a column of information from a third workbook into Column C of w1. It works.

Now I need to compare w1.column C (with Row 1 being a label if at all possible) to w2.Column E (also with Row 1 as a label). Note: Row 3 in 1 wb may not correspond to Row 3 in the other wb

When a match is found between the 2 workbooks, I need to copy the entry in w2.Column I into w1.ColumnF.

There will be many more records in w2 than in w1. I am only concerned with finding the data in w1 that matches w2.

It would also be great to indicate which records in w1 do not have a match in w2 as we will then need to update w2 with the correct information manually.

Marcie Fessler

[apo]

Hi..

Scripting.Dictionary would be good for that...

Can you attach your 2 workbooks so we don't have to recreate what you already have.. anonymise/cut down the data if you need to...

[Marcieface]

I have changed a few things about my original request.

w1 is still MasterTemplate but the column to compare is w1.worksheet2.Column A. .
w2 is still AllocatedList. The column to compare is w2.worksheet1.Column C.

When an entry in w1.worksheet2.ColumnA matches an entry in w2.worksheet1.ColumnC, the entry in w2.worksheet1.ColumnI (which is labeled Max Units on Shelf) should be copied into w1.worksheet2.ColumnD.

I've populated the UPC column in worksheet 2 of the Master Template just for this purpose. Typically it's done with a macro copying the information from a third workbook.


MasterTemplate.xlsmAllocatedList.xlsx

Marcie Fessler

[apo]

Hi..

Used autofilter instead.. it should do what you want.. although it took about 30 seconds to run on my machine..

Both Workbooks need to be open..

Please Login or Register  to view this content.