Compare 2 sheets, 2 columns, copy/paste matching data to third sheet.

Report
Posts
2
Registration date
Friday September 17, 2021
Status
Member
Last seen
September 20, 2021
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
Hello,

In response to this link https://ccm.net/forum/affich-756470-comparing-two-excel-sheets-and-copying-like-data-to-third-sheet

I am using this code, however, the data in Sheet 2 has 7000 rows of data, this is causing it to not respond ( I think) as it is stuck in running , when I click debug it points to end if


Sub RunMe()
Dim lRow, lrow2 As Long
Dim fValue As Range

Sheets("Sheet1").Select
lRow = Range("C1").End(xlDown).Row
lrow2 = Sheets("Sheet2").Range("E1").End(xlDown).Row

For Each cell In Range("C2:C" & lRow)
With Sheets("Sheet2").Range("E2:E" & lrow2)
Set fValue = .Find(cell.Value, LookIn:=xlValues)
If fValue Is Nothing Then
cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
Next cell

End Sub


System Configuration: Windows / Chrome 93.0.4577.82

2 replies

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Mustangboss,

Following is a code I wrote for one of my wife's work colleagues which I believe will do the task for you as well:-

Option Explicit
Sub Test()

Dim i As Long, v As Variant

Application.ScreenUpdating = False

        Sheet3.UsedRange.Offset(1).Clear
 
        With Sheet1
                For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
                    v = Application.Match(.Cells(i, "A").Value, Sheet2.Columns("A"), 0)
                    If Not IsError(v) Then
                        .Cells(i, "A").Interior.ColorIndex = 6
                    End If
                Next i
        End With

Test2

Application.ScreenUpdating = True

End Sub

Sub Test2()

       With Sheet1.[A1].CurrentRegion
                .AutoFilter 1, vbYellow, 8
                .Offset(1).EntireRow.Copy
                Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With

        Sheet1.Columns(1).Interior.ColorIndex = xlNone

End Sub


There are actually two codes with Test calling Test2. I split the two aspects of the code into two separate functions as they usually operate a lot quicker on large data sets when done this way. Loop type codes do have a tendency to get bogged down with large data sets.

The Test code uses the MATCH function to find matches in Column A of sheets 1 and 2 then high-lights the matches in Column A of sheet1 in yellow. Test then calls Test 2 which in turn filters Column A of sheet1 for the yellow fill colour and then transfers the relevant rows of data to sheet3.

I've attached a sample workbook with the code implemented and I've deliberately made the Column A data in sheet1 shorter than the Column A data in sheet2 by about 6K rows to hopefully simulate your data sets.
Please note that the codes work on Column A of both sheets so if you need the column references changed that can easily be altered.

Here is the link to the sample file:-

https://wetransfer.com/downloads/149d818bf37671a6d19c5b2ffba66e2820210920081533/8fac2e

Click on the TEST ME button to see how it works.

TrowaD actually wrote the code that you are currently using so perhaps he may come on board and possibly upgrade it for you, maybe.

I hope that this helps.

Cheerio,
vcoolio.
Posts
2
Registration date
Friday September 17, 2021
Status
Member
Last seen
September 20, 2021

Thanks very much for your time on this! I am getting a compile error when I try to run it, saying variable not defined.
It is pointing to Sub Test2() Any idea how to resolve this?
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Mustangboss,

As it works fine in the sample that I supplied, I'm assuming that the error has arisen in your actual workbook.
I'll need to see a sample of your actual workbook to try and resolve this for you so please upload a sample to a free file sharing site such as I have done in my last post then post the link to your file back here.
Make sure that the sample is an exact replica of your actual workbook and if your data is sensitive then please use dummy data.

Cheerio,
vcoolio.