Previous Topic Next Topic

How to compare two excel sheets and combine matching data

Closed
jfitz Posts 1 Registration date Monday August 14, 2017 Status Member Last seen August 14, 2017 - Aug 14, 2017 at 03:31 PM
vcoolio Posts 1365 Registration date Thursday July 24, 2014 Status Moderator Last seen March 28, 2023 - Aug 15, 2017 at 12:47 AM
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

Answer 1 / 1
vcoolio Posts 1365 Registration date Thursday July 24, 2014 Status Moderator Last seen March 28, 2023 252
Aug 15, 2017 at 12:47 AM
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.
0