Option Explicit
Sub test()
Dim a, i As Long, j As Long, w(), txt As String
With Sheets(1).Range("a10").CurrentRegion
a = Application.Index(.Value, Evaluate("row(1:" & _
.Rows.Count & ")"), Array(1, 2, 3, 4, 8, 12, 13, 14))
End With
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(a, 1)
txt = Join(Array(a(i, 1), a(i, 2), a(i, 3), a(i, 4)), Chr(2))
If Not .exists(txt) Then
.Item(txt) = VBA.Array(a(i, 5), a(i, 6), a(i, 7), a(i, 8))
End If
Next
With Sheets(2).Range("a4").CurrentRegion
With .Offset(1, 4).Resize(.Rows.Count - 1, .Columns.Count - 4)
.ClearContents
End With
a = .Value
End With
For i = 2 To UBound(a, 1)
txt = Join(Array(a(i, 1), a(i, 2), a(i, 3), a(i, 4)), Chr(2))
If .exists(txt) Then
w = .Item(txt)
For j = 5 To UBound(a, 2)
a(i, j) = w(j - 5)
Next
End If
Next
End With
Sheets(2).Range("a4").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub