Match data from one tab to another, and copy row to Third sheet

[HansV]

What do you want to happen if there are multiple matching rows?
For example, there are 2 rows on Sheet1 with 3315A/B and 52132, and 1 row on Sheet2 with those values.
And there are 7 rows on Sheet1 with 220200 and 44444, and also 7 rows on Sheet2 with those values.
Please indicate how exactly you want to handle such cases.

[bradjedis]

Hans,

I would like to have them copied over as well.

Thanks
Brad

[HansV]

Do you want the row with 3315A/B and 52132 copied twice (because it occurs twice on Sheet1) or once (because it occurs once on Sheet2)?

[bradjedis]

hmmmm. Go ahead and copy over, If need be I will Filter out.

Sorry this is such a pain.

[HansV]

This macro combines the earlier approaches:

Code: Select all

Sub CopyMatching()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim r1 As Variant
    Dim r2 As Long
    Dim r3 As Long
    Dim m As Long
    ' Change the sheet names!
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Sheet3")
    r3 = ws3.Range("A" & ws3.Rows.Count).End(xlUp).Row
    m = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    For r2 = 2 To m
        r1 = Evaluate("MATCH(1, ('" & ws1.Name & "'!A1:A1000='" & ws2.Name & _
            "'!A" & r2 & ")*('" & ws1.Name & "'!B1:B1000='" & ws2.Name & "'!B" & r2 & "), 0)")
        If Not IsError(r1) Then
            r3 = r3 + 1
            ws2.Range("A" & r2).EntireRow.Copy ws3.Range("A" & r3)
        End If
    Next r2
End Sub

The attached workbook (now a macro-enabled workbook) contains the result of running the macro.

Example 2.xlsm

[bradjedis]

Much Better! I will evaluate the results on my data.

Either way I will respond...