Find/Copy/Paste Issue

[troywahl]

I am brand new to anything to do with Excel programming via VBA. My issue sounds simple but I don't know how to fix it. It is for my job and I feel there is an easier way to do this.

I have a workbook that has 177,000 records. The example below doesn't show all the columns on the original and is just used as an example.

Main-List

Then I get updated workbooks like the following example with 1000s of changes.

Updates

Basically what I need to do is be able to take Updates workbook and copy it into the Main-List Workbook of 177,000 records overwriting the old data. The Banner ID/PIDM are unique numbers. Right now I am only able to find the Banner ID in Main workbook, then select the row and manually copy and paste over the data. I get these weekly and I feel there has to be an easier way than copying and pasting one at a time to update the records.

I apologize if I did this wrong but I have tried searching far and wide on the internet and just can't seem to find an easy way to do this. The original document has 39 columns and it is just a tedious process. Any help will be appreciated.

 

[Peter_SSs]

Re: Brand new to Excel need help with Find/Copy/Paste Issue

Welcome to the MrExcel board!

Give this a try in a copy of your workbook.

I have assumed that the two worksheets are in the same workbook. I have named them "Main" and "Updates". You will need to change the code below to match, or change the names of the sheets to match my code (at least to test the code).
I have also assumed that the data starts in cell A1 of each sheet and that the number of columns in each sheet is the same.

If the code below does not do what you want, please explain exactly is wrong and provide some small dummy sample data that we can copy to test with. We can't copy from an image like you provided above.
My signature block below has a link with several suggestions for providing sample data.

Sub Update()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, r As Long, cols As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Sheets("Main").Range("A1").CurrentRegion.Value
  cols = UBound(a, 2)
  For i = 2 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = i
  Next i
  b = Sheets("Updates").Range("A1").CurrentRegion.Value
  For i = 2 To UBound(b)
    If d.exists(b(i, 1) & "|" & b(i, 2)) Then
      r = d(b(i, 1) & "|" & b(i, 2))
      For j = 3 To cols
        a(r, j) = b(i, j)
      Next j
    End If
  Next i
  Sheets("Main").Range("A1").Resize(UBound(a), cols).Value = a
End Sub

 

[troywahl]

Re: Brand new to Excel need help with Find/Copy/Paste Issue

Welcome to the MrExcel board!

Give this a try in a copy of your workbook.

I have assumed that the two worksheets are in the same workbook. I have named them "Main" and "Updates". You will need to change the code below to match, or change the names of the sheets to match my code (at least to test the code).
I have also assumed that the data starts in cell A1 of each sheet and that the number of columns in each sheet is the same.

If the code below does not do what you want, please explain exactly is wrong and provide some small dummy sample data that we can copy to test with. We can't copy from an image like you provided above.
My signature block below has a link with several suggestions for providing sample data.

Sub Update()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, r As Long, cols As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Sheets("Main").Range("A1").CurrentRegion.Value
  cols = UBound(a, 2)
  For i = 2 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = i
  Next i
  b = Sheets("Updates").Range("A1").CurrentRegion.Value
  For i = 2 To UBound(b)
    If d.exists(b(i, 1) & "|" & b(i, 2)) Then
      r = d(b(i, 1) & "|" & b(i, 2))
      For j = 3 To cols
        a(r, j) = b(i, j)
      Next j
    End If
  Next i
  Sheets("Main").Range("A1").Resize(UBound(a), cols).Value = a
End Sub

Thanks!! That seemed to have worked on my test sheet. I will try it with a copy of my workbook at work when I get there. Thank you again and I will let you know if it works at work.