-3

I need to create a Macro to compare last names in columns on 2 separate worksheets, and when there matches with the last names copy and paste totals from a different column from one worksheet to another.

Sheet1 on left Sheet2 on right:

enter image description here

Xabier
  • 7,587
  • 1
  • 8
  • 20
jeffmuel
  • 1
  • 2

2 Answers2

0

If the surnames are unique then you could use a formula on Sheet one cell C1 enter the following and drop the formula down:

=IFERROR(VLOOKUP(B1,Sheet2!B:C,2,FALSE),"")

Or you could do it with VBA if you wanted to compare first name and second name, in case the surname is not unique:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
'declare and set your worksheet, amend as required
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A
LastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A

For i = 2 To LastRow2 'loop from row 2 to last of Sheet2
    If ws2.Cells(i, 3) <> "" Then 'if value on third column is not nothing
        Firstname = ws2.Cells(i, 1) 'get the first name
        Surname = ws2.Cells(i, 2) 'get surname
        For x = 2 To LastRow 'loop through sheet1
            If ws.Cells(x, 1) = Firstname And ws.Cells(x, 2) = Surname Then ws.Cells(x, 3) = ws2.Cells(i, 3)
            'compare the firstname and surname and if match then add the value from column C
        Next x
    End If
Next i
End Sub
Xabier
  • 7,587
  • 1
  • 8
  • 20
0

Very easy to do using SQL language, please check my answer for a similar question here: Link