Copying rows based on values in two columns

albie91

New Member
Joined
Apr 22, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.H1H2
S1abcd2
S4xyz503
S2asd33

W2:
Sl. no.H1H2
S3pqr10
S5asdfg503
S1abcd2
S4xyz11000

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
VBA Code:
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,887
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Apr 22, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
VBA Code:
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:
VBA Code:
Ary = .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp)).Value2

I'm not sure why this could be though.
 

albie91

New Member
Joined
Apr 22, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.H1H2
S1abcd2
S1pqrs50
S2asd33

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,887
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're quite right, I did miss the .Copy, try
VBA Code:
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.
 
Solution

albie91

New Member
Joined
Apr 22, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you so much. This works like a charm.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,887
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,277
Messages
5,674,799
Members
419,526
Latest member
ranjit446

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top