Selected Answer
McCoy
Provided your records have unique IDs (so no repeats in your Master file) and your senders use the same file format (with just one sheet, as in the files attached to your question), the VBA macro in the attached file can be used. If you don't have macros enabled (the default is not, please search the internet to find out how to do that).
In the attached revised file, you'll see the first row is expanded and there's a green button to "Open a file to import from". Click that and the code below will run.
It will open a single file you choose, use the VBA .Find method to locate (and update) each matching record in your Master file and shade the found records green in the file from the sender (so you can see any which couldn't be found, if an extra space of digit was added to the ID accidentally say). I've also added columns O and P so your master file can say the date and source of the update.
If you don't know VBA then the code below may blow your mind a bit but I've added comments (should show in red) so you can see that is happening:
Option Explicit
Sub ImportVacRecs()
Dim wsDest As Worksheet, wsImp As Worksheet, wsNm As String
Dim LstRw As Long, ImpLstRw As Long
Dim n As Long, ID As String, Fnd As Range, OutP As String
' say where updates go (destination file)
Set wsDest = ThisWorkbook.Worksheets("Raw")
' call function to get file path to import
wsNm = GetFile
' check a file was picked
If wsNm = "" Then
MsgBox "No file selected"
Exit Sub
End If
' if so, open that file
Workbooks.Open wsNm
' say where imports will come from (first sheet)
Set wsImp = ActiveWorkbook.Worksheets(1)
' pause updates (for speed)
Application.ScreenUpdating = False
' work out last used rows in destination and import files
LstRw = wsDest.Range("B" & Rows.Count).End(xlUp).Row
ImpLstRw = wsImp.Range("A" & Rows.Count).End(xlUp).Row
' Loop through records in import file
For n = 2 To ImpLstRw
' get the value from import file then attempt Find in destination
ID = wsImp.Cells(n, 1).Value
Set Fnd = wsDest.Range("B2:B" & LstRw).Find(ID, LookIn:=xlValues)
' check result of Find...
If Not Fnd Is Nothing Then
' if a matching cell is found, write record values from import file
Fnd.Offset(0, 6).Resize(1, 6).Value = wsImp.Cells(n, 1).Offset(0, 3).Resize(1, 6).Value
' add a record of when and where update is from
Fnd.Offset(0, 13).Value = Date
Fnd.Offset(0, 14).Value = wsImp.Parent.Name
' shade found records green in the import file
With wsImp
.Range(.Cells(n, 1), .Cells(n, 9)).Interior.Color = vbGreen
End With
' add to the output string
OutP = OutP & "Updated " & ID & " (row " & Fnd.Row & ")" & vbCr
Else
' if not found, just add to the output string
OutP = OutP & "NOT FOUND: " & ID & vbCr
End If
' got back to loop
Next n
' reveal results
Application.ScreenUpdating = True
'tell user what was updated and wasn't found)
wsNm = MsgBox(OutP, vbOKOnly, "Master file amended as follows...")
End Sub
The bold line calls a function to open a sender file and return the path/name of it:
Function GetFile() As String
Dim Fd As Office.FileDialog
Dim ImpFile As String
Set Fd = Application.FileDialog(msoFileDialogFilePicker)
' Open the file dialogue in this folder, allowing one file only
With Fd
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx?", 1
.Title = "Choose an Excel file to import records"
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.Path & "\"
If .Show = True Then
GetFile = .SelectedItems(1)
End If
End With
End Function
Suggest you try it out with your sample file (it may seem remarkably quick!) possibly adding some false records for test purposes then copy your 20,000 records to the Master file (being careful not to overwrite the button) and try again.
Hope this works for you- if so, please remember to mark this Answer as Selected and let me have a comment about the speed it works at on the real data set.
