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..

Private Sub CommandButton1_Click()
    Dim LastRow As Long, LastRow2 As Long, lr As Long, i As Long
    Dim Crit1 As String, v As Range

    Application.ScreenUpdating = False
    With ThisWorkbook
        LastRow = Sheets("Import to StoreProduct").Range("A" & Rows.Count).End(xlUp).Row
        LastRow2 = Workbooks("AllocatedList.xlsx").Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row

        For i = 2 To LastRow
            Crit1 = Sheets("Import to StoreProduct").Cells(i, 1).Value
            Sheets("Import to StoreProduct").Range("A1:G" & LastRow).AutoFilter Field:=1, Criteria1:=Crit1
            Workbooks("AllocatedList.xlsx").Sheets("Data").Range("A1:I" & LastRow2).AutoFilter Field:=3, Criteria1:=Crit1

            lr = Sheets("Import to StoreProduct").Range("A" & Rows.Count).End(xlUp).SpecialCells(12).Row
            Set v = Range("A1:A" & Range("A65536").End(xlUp).Row).Offset(1).SpecialCells(xlCellTypeVisible)

            If Workbooks("AllocatedList.xlsx").Sheets("Data").Range("I1:I" & LastRow2).SpecialCells(xlCellTypeVisible).Count > 1 Then
               Workbooks("AllocatedList.xlsx").Sheets("Data").Range("I2:I" & LastRow2).SpecialCells(xlCellTypeVisible).Copy _
                        Destination:=Sheets("Import to StoreProduct").Range("D" & v.Row)
            End If
        Next i
        If Sheets("Import to StoreProduct").AutoFilterMode Then ActiveSheet.AutoFilterMode = False
        Workbooks("AllocatedList.xlsx").Activate
        If Workbooks("AllocatedList.xlsx").Sheets("Data").AutoFilterMode Then ActiveSheet.AutoFilterMode = False
        .Activate
    End With
    Application.ScreenUpdating = True
End Sub