Previous Topic Next Topic

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

Closed
Mustangboss23 Posts 2 Registration date Friday September 17, 2021 Status Member Last seen September 20, 2021 - Updated on Sep 22, 2021 at 06:49 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Sep 20, 2021 at 10:13 AM
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
Related:

2 responses

Answer 1 / 2
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Updated on Sep 20, 2021 at 04:25 AM
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.
0
Mustangboss23 Posts 2 Registration date Friday September 17, 2021 Status Member Last seen September 20, 2021
Sep 20, 2021 at 10:04 AM
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?
0
Answer 2 / 2
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Sep 20, 2021 at 10:13 AM
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.
0