Code to copy row if two criteria match

[zkristen]

Hi! I have looked everywhere for help with a macro and am only halfway there. I am new here, so if I'm missing something, let me know! Thanks!

The main problem is that I have to sets of data. Each row in sheet2 should have a match in sheet1, but sometimes there is a row that is missing a match. I am matching two criteria, and sometimes there are duplicates. I need to know when there is a row in sheet2 without a match in sheet1.

Here is what I am thinking and what I have tried:

I have two sheets in the same workbook. If the values in columns D and F of Sheet 2 match the values in columns C and E (respectively) of Sheet 1, I want to copy the row from Sheet 1 (columns A to H are being used) to Sheet 2 starting in Column H of Sheet 2. The extra tricky part is there could be multiple matches. I want to copy each time there is a match without repeating. I want to do this for every row in Sheet 2. Depending on the data, there could be 100 rows or 10,000. I want to keep the macro the same and be able to use it with different data sets.

Here is the code I have tried. It starts replacing in column D instead of column H, and doesn't copy duplicates. It also only matches for one criteria.

Option Explicit


Sub cctest()
Dim NewDataRng As Range 'For Sheet1
Dim Cel As Range 'For Sheet1
Dim OldDataRng As Range 'For Sheet2
Dim MatchingValueCell As Range 'For Sheet2
Dim LastRow As Long


  With Sheets("Sheet1")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set NewDataRng = .Range("C2:C" & CStr(LastRow))
  End With
  
  With Sheets("Sheet2")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set OldDataRng = .Range("D2:D" & CStr(LastRow))
  End With
  
  For Each Cel In NewDataRng
    Set MatchingValueCell = OldDataRng.Find(What:=Cel.Value, _
              After:=OldDataRng.Cells(OldDataRng.Cells.Count))
    If Not MatchingValueCell Is Nothing Then _
      Cel.Resize(1, 8).Copy MatchingValueCell
  Next Cel


End Sub

Here's a sample file:
book3.xlsm

[SamT]

The "Copy to H" is easy

 Cel.Resize(1, 8).Copy MatchingValueCell.Offset(, 4)

The rest of your post is confusing

Your code checks to see if there is a match, but, you said:.

I need to know when there is a row in sheet2 [old data] without a match in sheet1 [new data].

And, you said:

Each row in sheet2 should have a match in sheet1

The code only checks one criteria, but you said:

I am matching two criteria,

I see no check for duplicates in the code, but you said:

Here is the code I have tried. It ... doesn't copy duplicates.

Logically, I would imagine that you want all new VALUES in New Data to be added to the Old Data Sheet, thereby making Old Data a comprehensive listing. That is pretty straight forward, even with multiple criteria, and your code would work, albeit slowly, by merely appending the new Values to the bottom of the old data.

One way to accomplish this is to merely append all the new darta to the Old Data, then sort Unique, in place. THe advatages are the it uses well optimized built-in Excel function, the disadvantage is that it checks all cells in the row, IOW, 8 criteria vs 2.



Can you clarify what you want?