Compare two excel files and bring the non existing entries from 1st excel file to 2nd excel file

[deba2020]

We have 2 excel files, we want to bring all the data from 1st excel file to 2nd excel file which are not there in 2nd excel file.
1st excel file name : Store Linking File.xlsm
2nd excel file name : Inward.xlsm
The existing code which we have written is taking much time because of the loops.
Please help with a code which will be more efficient than the existing one and takes less time to bring data from 1st file.

Stores Linking File.xlsm
	B
2	GRN
Sheet1

Inward.xlsm
	A
3	10:26:00
Sheet1

Existing code is as follows:

'module for updating GR details from Store linking file
Sub update_GRDetails1()
ActiveSheet.Unprotect Password:="123456"
Dim i, w1, w2 As Worksheet
Dim FirstR, LastR, n, founR, x, x2 As Long
Dim GRN1 As String
Dim FounD As Range
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    Workbooks.Open "\\bpild6987-01\Linking File\Stores Linking File.xlsm", ReadOnly:=True, UpdateLinks:=True
    ActiveWindow.Visible = True
    Set w1 = Workbooks("Stores Linking File.xlsm").Sheets("Sheet1")
    Set w2 = Workbooks("Inward.xlsm").Sheets("Sheet1")
    'Find the last Non Zero Cell in Date Column
        x2 = w1.Range("K" & Rows.Count).End(xlUp).Row
        Do Until w1.Range("k" & x2) <> 0
        x2 = x2 - 1
        Loop

    FirstR = w2.Columns("B:B").Find(What:="GRN").Row

   For x = FirstR + 2 To x2
  
   w1.Activate
       
     GRN1 = w1.Cells(x, 2) & w1.Cells(x, 4) & w1.Range("R" & x).Value
     w2.Activate
     Set FounD = w2.Columns("AZ:AZ").Find(What:=GRN1)
     If FounD Is Nothing Then
    
     w2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = w1.Range("B" & x).Value
     w2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = w1.Range("C" & x).Value
     w2.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = w1.Range("D" & x).Value
     w2.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = Mid(w1.Range("D" & x).Value, 3, 4)
     w2.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Value = w1.Range("E" & x).Value
     w2.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Value = w1.Range("F" & x).Value
     w2.Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).Value = w1.Range("K" & x).Value
     w2.Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Value = w1.Range("J" & x).Value
     w2.Cells(Rows.Count, 10).End(xlUp).Offset(1, 0).FormulaR1C1 = "=RC[-2]* RC[-1]"
     w2.Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Value = w1.Range("N" & x).Value
     w2.Cells(Rows.Count, 16).End(xlUp).Offset(1, 0).Value = w1.Range("O" & x).Value
     w2.Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).Value = w1.Range("S" & x).Value
     w2.Cells(Rows.Count, 2).End(xlUp).Offset(0, 50).Value = w1.Range("b" & x).Value & w1.Range("d" & x).Value & w1.Range("R" & x).Value
         
     End If
    
   Next x
  
   w2.Activate
  
   Workbooks("Stores Linking File.xlsm").Close savechanges:=False
  
   'Delete the rows in which GRN or Quantity is Zero
   LastR = w2.Cells(Rows.Count, 7).End(xlUp).Row
   For i = LastR To FirstR + 1992 Step -1
   If Cells(i, 2).Value = 0 Or Cells(i, 7).Value = 0 Then
   Cells(i, 6).EntireRow.Delete
   End If
   Next i

   Range(Range("A1993").End(xlDown).Offset(1, 0), Cells(Cells(Rows.Count, 7).End(xlUp).Row, 52)).Select
   Range("A1993:AZ1993").Copy
   Selection.PasteSpecial Paste:=xlPasteFormats
  
   ActiveSheet.Protect Password:="123456", AllowFiltering:=True
   ActiveWorkbook.Save
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True

End Sub

 

[NdNoviceHlp]

Not sure if this helps...

Compare 2 workbooks and find the differences using Array

I am trying 2 compare 2 workbooks loops but it is taking time. I want to compare 2 workbooks and find the differences Colud you pleas help on this. Below is the code for loops Public Function ExcelCmp(firstFile, secondFile, resultFile) ' Declaring varaibles Dim objExcel1, objExcel2...

www.mrexcel.com

The code at the link compares 2 workbooks and colours the cells that are different. U can have data in workbook#1 that is not in workbook#2 AND U can have data that's in workbook#2 that's not in workbook#1. I'm not sure that applies here for U? Do U want to only check workbook#1 for data that's not in workbook#2? What if data exists but is not the same? The code can be adjusted to update workbook#2 and/or workbook#1 rather than changing the cell colour fairly easily. As I recall, the code is fairly quick but will be dependent upon how much data is in the 2 workbooks. HTH. Dave

 

[deba2020]

Yes, we are only comparing data from workbook1 with data from workbook2, and if the data from workbook1 is missing from workbook2, we will add it to workbook2.
And in workbook2 we are trying to copy the format of above cells and apply it to the newly added data.

 

[NdNoviceHlp]

After reviewing your code more carefully, your thread title is somewhat misleading. You are only comparing 1 sheet and only certain rows and certain columns of the 2 workbooks. I don't think that the linked code will help with this. I do have some suggestions..
1)When declaring variables...

Dim FirstR, LastR, n, founR, x, x2 As Long

The statement above only declares the "x2" as Long, the rest remain Variant ie. U need to declare each variable.
2)U start your code with...

On Error Resume Next

which does not address any errors, may produce erroneous results and who knows, maybe extends the time of code execution. I would guess U have included this in case some comparison cells are on error? It would be better to code to avoid errors. However, if U remove this code and U are going to mess with the screen updating, alerts, events and calculations, then U will need to code to ensure that if an error does occur and the code is not fully executed, then these are returned to normal.
3)U may want to trial controlling the events and calculations which will improve speed...

'at the start
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
'at the end
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Hopefully, some of this is helpful. Perhaps others have alternate suggestions. Dave