How to compare two excel sheets and combine matching data [Closed]

Report
Posts
1
Registration date
Monday August 14, 2017
Status
Member
Last seen
August 14, 2017
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,

I'm looking for a macro that will match data in column A from sheet 1 to the data in column A from sheet 2 and copy the corresponding rows from sheet 1 and 2 to sheet 3.

(A1 from sheet 1 = A1 from sheet 2 --> copy rows from sheet 1 and sheet 2 to sheet 3)

Thank you in advance for the help!
Jfitz

1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
212
Hello Jfitz,

Try the following code, placed in a standard module and assigned to a button:-

Sub FindMatch()

    Dim lr As Long
    Dim fValue As Range
    Dim c As Range

lr = Range("A" & Rows.Count).End(xlUp).Row

For Each c In Sheet1.Range("A2:A" & lr)
    Set fValue = Sheet2.Columns("A:A").Find(c.Value)
        If fValue Is Nothing Then GoTo Nextc
            If c.Value = fValue.Value Then
                c.EntireRow.Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
                    fValue.EntireRow.Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
            End If
Nextc:
Next c

Sheet3.Select

End Sub


I hope that this helps.

Cheerio,
vcoolio.