Macro to compare and copy data from one sheet to another, taking a lot of time

Question

I used this macro to copy contents from one Excel sheet to another, by comparing two columns and finding the corresponding cell. The problem is that this macro takes a long time (about three days) to complete. Both sheets compare about 4,000,000 records.

Can someone please help me 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 

Answer 1

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 

Answer 2

For 4 million records between 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 in Sheet1 that you want to write to by placing the name on the first row of each column. As an example, let me call them F1 , F2 , F3 , F4 and F5 . Also specify a column with common data between Sheet1 and Sheet2; for example, we will call it F0 .

Then, if your version of Office allows this, you can publish this expression:

 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 

Answer 3

Get the whole sheet at once using

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

and compare the values ​​locally

EDIT-1
The Google Apps documentation script 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 large number of ranges, data should be received in ONE call and processed locally