troywahl

New Member
Joined
Mar 7, 2017
Messages
2
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.

Rich (BB code):
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

New Member
Joined
Mar 7, 2017
Messages
2
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.

Rich (BB code):
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,454
Messages
5,601,764
Members
414,471
Latest member
cchunyadi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top