Updating large data

[eedrah]

Our company have a large excel list (about 100 000 rows and 20 columns) we need to maintain. We receive updates from a third party every month (for some of the fields only) and need some way to check, for each entry, if there has been an update. I have made a simple macro for this in excel that will check one entry at a time: It works, but it takes days to loop through it all. Does anyone have a better solution - in excel or any other program?

Typically, our existing sheet will have columns like:
Unique ID - Vessel ID - Vessel name - Vessel type - Owner - Product delivered etc

The update we receive will contain:
Vessel ID - Vessel name - Vessel type - Owner

I can relate the two sheets using Vessel ID, but only some of the fields will be updated every month.

 

[eedrah]

That gives Out of memory on Ary = Rws.Range("M1:FU" & Lr).Value2

 

[Fluff]

Doesn't surprise me much, in-fact I was surprised when it worked before.
Give this a go

Sub UpdateFile()
   Dim Cl As Range
   Dim Uws As Worksheet, Rws As Worksheet
   
   Set Uws = Sheets("Update")
   Set Rws = Sheets("Pcode")
   With CreateObject("scripting.dictionary")
      For Each Cl In Uws.Range("A2", Uws.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Application.Index(Cl.Offset(, 1).Resize(, 10).Value, 1, 0)
      Next Cl
      For Each Cl In Rws.Range("M2", Rws.Range("M" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then
            Cl.Offset(, 1).Value = .Item(Cl.Value)(4)
            Cl.Offset(, 2).Value = .Item(Cl.Value)(6)
         End If
      Next Cl
   End With
End Sub

 

[eedrah]

Looks promising! I will run some more extensive tests tomorrow. Thank you SO much for fantastic help!!

 

[Fluff]

Glad to help & thanks for the feedback