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.

 

[Foz1980]

Can you post the VBA code you are using for your macro?

 

[eedrah]

Can you post the VBA code you are using for your macro?

This is the simple code I have so far. It works, but takes forever. Note that the below is for updating one field only - in the final version I want to update around 15 fields when a match is found:

Dim lastrow As Long
Dim lastrowimport As Long
Dim found As Integer

lastrow = Worksheets("Register").Cells(Rows.Count, "A").End(xlUp).Row 'About 100 000 rows
lastrowupdate = Worksheets("Update").Cells(Rows.Count, "A").End(xlUp).Row 'About 5 000 rows

For i = 2 To lastrow
    For j = 2 To lastrowupdate
        If Worksheets("Register").Cells(i, 13).Value = Worksheets("Update").Cells(j, 1) Then 'Check if the IDs match
            Worksheets("Register").Cells(i, 14).Value = Worksheets("Update").Cells(j, 4).Value 'Update the "name" field if they match
            GoTo skip: 'Go on to next item if they match
        End If
    Next j
skip:
    Application.StatusBar = "Progress: " & i & " of " & lastrow
    DoEvents
Next i

 

[Fluff]

Will the same Vessel ID occur more than once in the update file?
Also which columns from the Update need to go to which columns in the register?

 

[eedrah]

Will the same Vessel ID occur more than once in the update file?
Also which columns from the Update need to go to which columns in the register?

No, the same Vessel ID will only occur once in the update file.
I have listed one of the columns (column 4 in the update goes to 14 in register). There are more columns, but I can add these later it I can find a quicker way to run this.

 

[Fluff]

Do you have any formulae, or formatting on the Register sheet that needs to be preserved?

 

[eedrah]

Do you have any formulae, or formatting on the Register sheet that needs to be preserved?

No, it is basically standard text with no special formatting.

 

[Fluff]

How about

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("Pcode")
   Ary = Rws.UsedRange.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(, [COLOR=#ff0000]10[/COLOR]).Value, 1, 0)
      Next Cl
      For i = 2 To UBound(Ary)
         If .exists(Ary(i, 13)) Then
            Ary(i, 14) = .Item(Ary(i, 13))(3)
            Ary(i, 15) = .Item(Ary(i, 13))(4)
         End If
      Next i
   End With
   Rws.UsedRange.Clear
   Rws.Range("A1").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub

Change the value in red to match the number of columns in the update file.
This will copy col D of the update to col N on register & col E to col O

 

[eedrah]

Thanks. It looks promising when I try with a few rows (and when I make sure all cells are formatted to text). But with all I get "Out of memory". So:
- Could the out of memory be because of too many rows, or could it be because some cells are not standard text (I get Out of memory error when I do not select Text for all cells as well)

 

[Fluff]

How many columns do you have in the register sheet?