Macro to compare column and copy & paste the data between 2 worksheets

[Manish_Gupta]

Hello Guys,

I need help to create a Macro which compares the “Customer ID” column in attached both files (Sample1.xlsx and Sample2.xlsx).

If same “Customer ID” is found in Sample2.xlsx file then value present in “Chassis No.” column against that customer id get copied into the “New Chassis No. 1” column of Sample1.xlsx file.

If same “Customer ID” is found again in Sample2.xlsx file then value present in “Chassis No.” column against that customer id get copied into the “New Chassis No. 2” column of Sample1.xlsx file.

For more understanding, please refer the Columns marked as an Yellow in Sample1.xlsx file.

Any help will be appreciated a lot.

Thanks,

[hemesh]

Hello Manish, Find attached !

you need to have both books open!
if you have different workbook and sheet name then do edit code.

Sub compareColumn()

Workbooks("Sample1").Sheets("Data").Range("B2").Select 'selects cell B2 in Sample1

Do While ActiveCell.Value <> "" 'loop continues till there is a value in sample1 range B:B
x = 0
    For i = 2 To 10 ' ((Remove 10 and add number of rows in Book - sample2 in range B)
        If ActiveCell.Value = Workbooks("sample2").Sheets("data").Range("B" & i).Value Then
        x = x + 1
        ActiveCell.Offset(0, x + 4).Value = Workbooks("sample2").Sheets("data").Range("B" & i).Offset(0, 4).Value
        End If
    Next
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Click "*" add rep icon in the bottom left corner of my post if this helps!