Compare two data list in different sheets and copy the difference to another sheet.

[Harshil Mehta]

I want to check if the ID in sheet No.2, column B (Refer attached) is present in sheet No. 3, column B and copy the difference to sheet No.5, Range B11 along with NAME, COUNTRY, REGION & SECTOR.

The below code only copies the ID to sheet No.5, Range B11.

I want to edit the code that copies ID, NAME, SECTOR, COUNTRY & REGION to sheet No.5

Sub x()

Dim v1, v2, v3(), i As Long, j As Long

v1 = Sheet2.Range("B11", Sheet2.Range("B" & Rows.Count).End(xlUp)).Value
v2 = Sheet3.Range("B11", Sheet3.Range("B" & Rows.Count).End(xlUp)).Value

ReDim v3(1 To UBound(v1, 1))

For i = LBound(v1) To UBound(v1)
    If IsError(Application.Match(v1(i, 1), v2, 0)) Then
        j = j + 1
        v3(j) = v1(i, 1)
    End If
Next i

Sheet5.Range("B11").Resize(j) = Application.Transpose(v3)

End Sub

Please help me with the above code.

 

[Fluff]

How about

Sub x()

Dim v1, v2, v3(), i As Long, j As Long, k As Long
Dim Dic As Object

Set Dic = CreateObject("scripting.dictionary")
v1 = Sheet2.Range("B11:F" & Sheet2.Range("B" & Rows.Count).End(xlUp).Row).Value2
v2 = Sheet3.Range("B11", Sheet3.Range("B" & Rows.Count).End(xlUp)).Value2

ReDim v3(1 To UBound(v1, 1), 1 To 5)

For i = LBound(v2) To UBound(v2)
   Dic.Item(v2(i, 1)) = Empty
Next i
For i = LBound(v1) To UBound(v1)
   If Not Dic.Exists(v1(i, 1)) Then
        j = j + 1
        For k = 1 To 5
            v3(j, k) = v1(i, k)
        Next k
    End If
Next i

Sheet5.Range("B11").Resize(j, 5) = v3

End Sub

 

[Harshil Mehta]

How about

Sub x()

Dim v1, v2, v3(), i As Long, j As Long, k As Long
Dim Dic As Object

Set Dic = CreateObject("scripting.dictionary")
v1 = Sheet2.Range("B11:F" & Sheet2.Range("B" & Rows.Count).End(xlUp).Row).Value2
v2 = Sheet3.Range("B11", Sheet3.Range("B" & Rows.Count).End(xlUp)).Value2

ReDim v3(1 To UBound(v1, 1), 1 To 5)

For i = LBound(v2) To UBound(v2)
   Dic.Item(v2(i, 1)) = Empty
Next i
For i = LBound(v1) To UBound(v1)
   If Not Dic.Exists(v1(i, 1)) Then
        j = j + 1
        For k = 1 To 5
            v3(j, k) = v1(i, k)
        Next k
    End If
Next i

Sheet5.Range("B11").Resize(j, 5) = v3

End Sub

This is super cool. Can we paste the same data set (pasted in Sheet5) to sheet3, Range B11 last row used?
Note: Sheet3, Range B11 contains data

 

[Fluff]

How about

Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(j, 5) = v3

 

[Harshil Mehta]

How about

Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(j, 5) = v3

This is awesome. Thank you so much.