Find values based on the same SKU on two different sheets

[Ovisele]

Hey All,

I am trying to compare two sheets, based on the same SKU and a condition (SKU to be deleted) and return only the results that are complying to the rule.

The idea is the following:
1. All the products are in the 17k products sheet
2. In the 12k products sheet are the products that were successfully imported in Wordpress
3. The 12k products sheet has in column G a "delete" indication
4. I want to compare the two sheets and return only the products that were not imported and has not to be deleted (basically, the rest of the values that I need to import).

The file is here: Work File Products.xlsx

Any ideas? Many thanks in advance,
O.

 

[mumps]

Please upload a revised file without any SKU duplicates in both sheets.

 

[Ovisele]

Please upload a revised file without any SKU duplicates in both sheets.

Kindly find the document here Work File Products.xlsx

 

[mumps]

Add a sheet and name it "Not Imported". Try this macro:

Sub CompareSKU()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Not Imported")
    desWS.UsedRange.ClearContents
    Set ws1 = Sheets("17k products")
    Set ws2 = Sheets("12k products")
    Dim i As Long, v1 As Variant, v2 As Variant
    v1 = ws1.Range("A2", ws1.Range("A" & Rows.Count).End(xlUp)).Value
    v2 = ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp)).Resize(, 7).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v2, 1)
            If Not .Exists(v2(i, 1)) Then
                .Add CStr(v2(i, 1)), Nothing
            End If
        Next i
        For i = 1 To UBound(v1, 1)
            If Not .Exists(CStr(v1(i, 1))) Then
                ws1.Rows(i + 1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[Ovisele]

Add a sheet and name it "Not Imported". Try this macro:

Sub CompareSKU()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Not Imported")
    desWS.UsedRange.ClearContents
    Set ws1 = Sheets("17k products")
    Set ws2 = Sheets("12k products")
    Dim i As Long, v1 As Variant, v2 As Variant
    v1 = ws1.Range("A2", ws1.Range("A" & Rows.Count).End(xlUp)).Value
    v2 = ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp)).Resize(, 7).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v2, 1)
            If Not .Exists(v2(i, 1)) Then
                .Add CStr(v2(i, 1)), Nothing
            End If
        Next i
        For i = 1 To UBound(v1, 1)
            If Not .Exists(CStr(v1(i, 1))) Then
                ws1.Rows(i + 1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

Absolutely fantastic, worked like a charm! You saved my day Much appreciated!

 

[mumps]

You are very welcome.