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]

How many columns do you have in the register sheet?

Ehm... 171
76 on the Update part, so I can for sure remove some before updating.

 

[Fluff]

On the register sheet, which is the first & last column to be updated?
So Col N has the value to be checked, will there be any columns before that, that need changing & what is the last column to be changed.

 

[eedrah]

On the register sheet, which is the first & last column to be updated?
So Col N has the value to be checked, will there be any columns before that, that need changing & what is the last column to be changed.

There are several ones on the register sheet that will be updated, and they are not adjacent. I will try to go through all tomorrow. Perhaps I can just remove the ones that do not need updating, and add them back when I am done. Any way to run this without changing format to text, you think?

 

[Fluff]

Can you please answer my question?

 

[eedrah]

Double checked, and M will have the value to be checked, N will be the first column updated and FU the last one.

 

[Fluff]

Thanks for that.
Try this to see if you still get the out of memory warning.

Sub chk()
   Dim Ary As Variant
   
   Ary = Range("M1", Range("FU" & Rows.Count).End(xlUp)).Value
End Sub

 

[eedrah]

No errors when running chk

 

[Fluff]

Ok, try this

Sub UpdateFile()
   Dim Cl As Range
   Dim Uws As Worksheet, Rws As Worksheet
   Dim Ary As Variant
   Dim i As Long
   
   Set Uws = Sheets("Update")
   Set Rws = Sheets("Register")
   Ary = Rws.Range("M1", Rws.Range("FU" & Rows.Count).End(xlUp)).value2
   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 i = 2 To UBound(Ary)
         If .exists(Ary(i, 1)) Then
            Ary(i, 2) = .Item(Ary(i, 1))(4)
            Ary(i, 3) = .Item(Ary(i, 1))(6)
         End If
      Next i
   End With
   Rws.UsedRange.Clear
   Rws.Range("M1").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub

This will update Cols N & O

 

[eedrah]

It doesn't seem to work. Only thing that happens is that everything disappears except the Register headings in columns M:FU

 

[Fluff]

Ok,how about

Sub UpdateFile()
   Dim Cl As Range
   Dim Uws As Worksheet, Rws As Worksheet
   Dim Ary As Variant
   Dim i As Long, Lr As Long
   
   Set Uws = Sheets("Update")
   Set Rws = Sheets("Pcode")
   Lr = Rws.Range("M" & Rows.Count).End(xlUp).Row
   Ary = Rws.Range("M1:FU" & Lr).value2
   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 i = 2 To UBound(Ary)
         If .exists(Ary(i, 1)) Then
            Ary(i, 2) = .Item(Ary(i, 1))(4)
            Ary(i, 3) = .Item(Ary(i, 1))(6)
         End If
      Next i
   End With
   Rws.Range("M:FU").EntireColumn.Clear
   Rws.Range("M1").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub