• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

comparing three columns and copy paste data

L

LearnExcel&VBA

Member
Is there any fastest of comparing three columns and copy paste data when matches on large data sets . I hope to get some help.

Code: 
Dim ws As Worksheet
Dim ws2 As Worksheet

Set ws = Worksheets("Sheet3")
Set ws2 = Worksheets("Sheet2")

    Dim a As String, b As String, c  As Date
    For i = 3 To ws.Cells(ws.Rows.Count, 14).End(xlUp).Row

        a = ws.Cells(i, 14).Value
        b = ws.Cells(i, 15).Value
        c = ws.Cells(i, 16).Value

        For j = 3 To ws2.Cells(ws2.Rows.Count, 98).End(xlUp).Row
        
            If ws2.Cells(j, 98).Value = a _
               And ws2.Cells(j, 103).Value = b _
               And ws2.Cells(j, 114).Value = c _
               Then
                ws2.Cells(j, 120).Value = ws.Cells(j, 18).Value
            End If
        Next j

    Next i
 
Sorry that i could not upload a sample file here it is..

There are about more than 10k rows in the file where the code works for specific similar values if finds then copy paste.
 

Attachments

  • Test1 (1).xlsm
    144.8 KB · Views: 3
Yes the sheet has lot of undeleted rows. But I have data on these rows that i have removed to share an example. tbh i did not get your point MYTH.
 
One way to use those Your someway removed rows to get max rows and
other way is to use only 'visible' rows.
There is huge difference between those max rows.
... and many times, if more eg rows, it will take more time.
You could see/find out that from my sample code too.
 
You must log in or register to reply here.
Back
Top