Macro to compare and copy data from one sheet to another taking a long time

Question

I used this macro to copy contents from one Excel sheet to another, by comparing two columns and finding a matching cell. The problem is that this macro is taking a long time (close to three days) to complete. There are close to 4,00,000 records in both the sheets to compare against.

Can someone please help me to make things faster?

    Option Explicit
    Sub MatchAndCopy()

       Dim sheet01 As Worksheet, sheet02 As Worksheet
       Dim count As Range, matchingCell As Long
       Dim RangeInSheet1 As Variant
       Dim RangeInSheet2 As Variant

       Application.ScreenUpdating = False
       Application.DisplayStatusBar = True

       Set sheet01 = Worksheets("Sheet1")
       Set sheet02 = Worksheets("Sheet2")
       Set RangeInSheet1 = sheet01.Columns(1)
       Set RangeInSheet2 = sheet02.Range("A2", sheet02.Range("A" & Rows.count).End(xlUp))


       For Each count In RangeInSheet2
         matchingCell = 0
         On Error Resume Next
         matchingCell = Application.Match(count, RangeInSheet1, 0)
         On Error GoTo 0
         If matchingCell <> 0 Then
           Application.StatusBar = "Please wait while data is being copied, Processing count : " & count
           sheet01.Range("F" & matchingCell).Value = count.Offset(, 1)
           sheet01.Range("G" & matchingCell).Value = count.Offset(, 2)
           sheet01.Range("H" & matchingCell).Value = count.Offset(, 3)
           sheet01.Range("I" & matchingCell).Value = count.Offset(, 4)
           sheet01.Range("J" & matchingCell).Value = count.Offset(, 5)
         End If
       Next count

       Application.StatusBar = False
       Application.ScreenUpdating = True

    End Sub

score 0 · Accepted Answer · answered Jul 28 '14 at 16:07

Should be faster:

Sub MatchAndCopy()

    Dim sheet01 As Worksheet, sheet02 As Worksheet
    Dim c As Range, matchingCell As Long
    Dim RangeInSheet1 As Range
    Dim RangeInSheet2 As Range
    Dim dict As Object, tmp
    Set dict = CreateObject("scripting.dictionary")

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True

    Set sheet01 = Worksheets("Sheet1")
    Set sheet02 = Worksheets("Sheet2")

    Set RangeInSheet1 = sheet01.Range(sheet01.Range("A2"), _
              sheet01.Cells(Rows.count, 1).End(xlUp))
    Set RangeInSheet2 = sheet02.Range(sheet02.Range("A2"), _
              sheet02.Cells(Rows.count, 1).End(xlUp))

    'populate dictionary...
    For Each c In RangeInSheet1.Cells
        tmp = c.Value
        If Not dict.exists(tmp) Then
            dict.Add tmp, c.Row
        End If
    Next c

    For Each c In RangeInSheet2.Cells
      tmp = c.Value
      If dict.exists(tmp) Then
        Application.StatusBar = "Please wait while data is being copied," & _
                                " Processing count : " & c.Row
        sheet01.Cells(dict(tmp), "F").Resize(1, 5).Value = _
                c.Offset(0, 1).Resize(1, 5).Value
      End If
    Next c

    Application.StatusBar = False
    Application.ScreenUpdating = True

End Sub

Add a reference to **Microsoft Scripting Runtime** (**Tools -> References...**). Then you can write `Dim dict As New Scripting.Dictionary` instead of `Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")`. — Zev Spitz, Sep 09 '15 at 21:48

score 0 · Answer 2 · edited May 23 '17 at 12:06

For 4 million records between the two sheets, use a database. Excel is not a database.

If you insist on treating Excel as a database, I would suggest using ADODB. See this answer for a similar problem and solution.

Name each of the columns on Sheet1 that you want to write to, by putting the name in the first row of each column. For the example, let's call them F1,F2,F3,F4 and F5. Also, name the column with the shared data between Sheet1 and Sheet2; for the example we'll call it F0.

Then, if your version of Office allows it, you can issue this statement:

UPDATE [Sheet1$]
INNER JOIN [Sheet2$] ON [Sheet1$].F0 = [Sheet2$].F0
SET 
    [Sheet1$].F1 = [Sheet2$].F1,
    [Sheet1$].F2 = [Sheet2$].F2,
    [Sheet1$].F3 = [Sheet2$].F3,
    [Sheet1$].F4 = [Sheet2$].F4,
    [Sheet1$].F5 = [Sheet2$].F5

If not, you can use the CopyFromRecordset method with the recordset generated from the following SQL statement:

SELECT s1.F0, 
    Iif(s2.F0 Is Not Null, s2.F1, s1.F1),
    Iif(s2.F0 Is Not Null, s2.F2, s1.F2),
    Iif(s2.F0 Is Not Null, s2.F3, s1.F3),
    Iif(s2.F0 Is Not Null, s2.F4, s1.F4),
    Iif(s2.F0 Is Not Null, s2.F5, s1.F5)
FROM [Sheet1$] AS s1
LEFT JOIN [Sheet2$] AS s2 ON s1.F0 = s2.F0

SoftwareTester · Answer 3 · 2014-07-28T16:06:08.060

-1

Get the whole sheet at once using

var values = sheet.getDataRange().getValues();

and compare values locally

EDIT-1
Google Apps script documentation https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet provides the following example for getDataRange()

Returns a Range corresponding to the dimensions in which data is present. This is functionally equivalent to creating a Range bounded by A1 and (Range.getLastColumn(), Range.getLastRow()).


var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This represents ALL the data
 var range = sheet.getDataRange();
 var values = range.getValues();

 // This logs the spreadsheet in CSV format with a trailing comma
 for (var i = 0; i < values.length; i++) {
   var row = "";
   for (var j = 0; j < values[i].length; j++) {
     if (values[i][j]) {
       row = row + values[i][j];
     }
     row = row + ",";
   }
   Logger.log(row);
 }

Instead of using a lot of ranges, data should be obtained in ONE call and processed locally

edited Jul 28 '14 at 16:06

answered Jul 28 '14 at 15:52

SoftwareTester

1,048
1
10
25

That is not VBA. VB.net? – Tim Williams Jul 28 '14 at 15:54
@SoftwareTester As Tim mentioned, I don't think the code you gave is VBA. I want to do this in excel, as a macro(using VBA) – unrahul Jul 28 '14 at 16:02
OK, I didn't understand you want to use VBA. But the principle of retrieving the data of a range ONCE holds for VBA as well. I've used VBA a lot in the past and it does make a big difference obtaining a range in one time or numerous times. In VBA you can also findout what lastrow and column have been used – SoftwareTester Jul 28 '14 at 16:09

Macro to compare and copy data from one sheet to another taking a long time

3 Answers3

Linked