Copying rows based on values in two columns

[albie91]

Hello all,
I could use some help.

I have two worksheets "W1" and "W2", each with multiple rows and columns, whose numbers may be subject to change. I need to go row by row in "W2", starting from row 2 and compare the values in columns A and C in "W2" to the columns A and C of every row in "W1", starting from row 2 respectively. If the value in "W2"'s column A is unique, then I need to copy the entire row to "W1". If a value of column "A" in one of the rows of "W2" is already existing in "W1", I need to check if the corresponding column C also matches. If the value in C is unique, then I need to copy this row to "W1" as well.
For example:
W1:

Sl. no.	H1	H2
S1	abcd	2
S4	xyz	503
S2	asd	33

W2:

Sl. no.	H1	H2
S3	pqr	10
S5	asdfg	503
S1	abcd	2
S4	xyz1	1000

So in the above case, the rows starting with S3, S5 and S4 from "W2" should be added to "W1", and the row starting with "S1" will be ignored, since it already exists. The comparison has to be done for Columns A and C.
I have the following code so far

Sub RowTransfer()
Application.ScreenUpdating = False
    Dim lastRow As Long
    lastRow = Sheets("W2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rnge As Range
    Dim valCheck As Range
    Dim valCheckC As Range
    For Each rnge in Sheets("W2").Range("A2:C" & lastRow)
        Set valCheck = Sheets("W1").Range("A:A").Find(rnge, lookIn:=xlValues, lookat:=xlWhole)
        Set valCheckC = Sheetws("W1").Range("B:B").Find(rnge, lookIn:xlValues, lookat:=xlWhole)
        If valCheck Is Nothing And valCheckC Is Nothing Then
            rnge.EntireRow.Copy
            Sheets("W1").Cells(Rows.Count, "A").End(xlUp).Offset(1,0).PasteSpecial xlPasteValues
        End If
    Next rnge
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

I think I understand that my "And" statement won't do the work, but I am not sure how to go ahead with this. I'm very new to Excel VBA and cannot seem to find a way to resolve this.

Hope you can help me.
Thank you so much.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub albie()
   Dim Ary As Variant
   Dim r As Long
   Dim Dic As Object
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   
   With Sheets("W1")
      Ary = .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   For r = 1 To UBound(Ary)
      Dic(Ary(r, 1)) = Ary(r, 3)
   Next r
   
   For Each Cl In Sheets("W2").Range("A2", Sheets("W2").Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value) Then
         Cl.EntireRow
         Sheets("W1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
      ElseIf Dic(Cl.Value) <> Cl.Offset(, 2).Value Then
         Cl.EntireRow
         Sheets("W1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
      End If
   nexxt Cl
   Application.CutCopyMode = False
End Sub

Although this won't work on a Mac.

 

[albie91]

Hello,
Thank you for the help! I am using it for Windows at the moment.
However, I have a couple of questions: first, I'm guessing that the full statement should be

Cl.EntireRow.Copy

, right?

Also, when I execute the above code, I get a runtime error 1004, Application-defined or object-defined error, and the following line is highlighted:

Ary = .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp)).Value2

I'm not sure why this could be though.

 

[albie91]

I also just noticed something that I had not anticipated earlier. It is possible that the worksheet "W1" can have the same value of "Sl. no." multiple times. For example:
W1:

Sl. no.	H1	H2
S1	abcd	2
S1	pqrs	50
S2	asd	33

If this is the case, I don't think we can use the dictionary, right? Since it is not necessary that the first column is always unique.

 

[Fluff]

You're quite right, I did miss the .Copy, try

Sub albie()
   Dim Ary As Variant
   Dim r As Long
   Dim Dic As Object
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   
   With Sheets("W1")
      Ary = .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For r = 1 To UBound(Ary)
      Dic(Ary(r, 1) & "|" & Ary(r, 3)) = Empty
   Next r
   
   For Each Cl In Sheets("W2").Range("A2", Sheets("W2").Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value & "|" & Cl.Offset(, 2).Value) Then
         Cl.EntireRow.Copy
         Sheets("W1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
      End If
   Next Cl
   Application.CutCopyMode = False
End Sub

This also deals with the multiple entries.

 

[albie91]

Thank you so much. This works like a charm.

 

[Fluff]

You're welcome & thanks for the feedback.