Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Bulk Update of Customer Status With Unique Customer IDs

0

Hi!

My question is how do you update multiple cell data all at once? The scenario below is just for sample purposes....

In WorkbookA>Raw sheet, I have a list of about 200,000 people with unique IDs that I can use to search, and the following columns:

- Name

- Was vaccinated (yes/no)

- Vaccination status (N/A, 1st, 2nd, booster1, booster2)

On a daily basis, 10 different people will send their individual workbooks (only option for now) containing the same columns but with updates to the columns "Was vaccinated" and "Vaccination status". Is there a way to update these 2 columns without putting formulas to these 2 columns in WorkbookA?

I tried using vlookup and index/match on each cell of the two columns, however, the file takes about 1 hour as soon as I turn on the calculation.

Is there a way to achieve this bulk update?

Answer
Discuss

Discussion

Hi and welcome to the Forum 

With so many records, I'd suggest using VBA (rather than formulae) to do the updating, using arrays to maximise the speed. Please edit your question and use the Add Files button to attach representative Excel files (but no real personal data) and some test records (but not 20,000!). If you do that today, I'll try to look at it tomorrow. 
John_Ru (rep: 4282) Oct 1, '22 at 6:04 am
Thanks, John_Ru.

I just uploaded 3 sample files:
- MasterFile: The main file that has the list of all the employees. The vaccination information (columns H to M) get updated based on the submitted data.
Sta. Ana September 23 2022: Submitted data from one of the 10 senders on September 23.
Sasa_September 23, 2022: Submitted data from one of the 10 senders on September 23.

Note: The 10 senders would send updates on a daily basis.

Thanks, again, John. You're a life saver!
mccoytan (rep: 2) Oct 2, '22 at 5:08 am
Thanks McCoy

Those files make sense but I ran out of time (will return tomorrow my time).

Are you happy to open each "submitted data" file so it can be imported (by the VBA macro) into the Master file?
John_Ru (rep: 4282) Oct 2, '22 at 8:51 am
Hi, John,

Yes, I am open to that. Thanks, in advance!
mccoytan (rep: 2) Oct 2, '22 at 8:54 am
You'll see that my Answer does not use arrays (as I suggested above) but it should be fast enough. Let me know please (in the Discussion below the Answer). Thanks. 
John_Ru (rep: 4282) Oct 3, '22 at 4:39 pm
Add to Discussion

Answers

0
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.

Discuss

Discussion

McCoy. Did you get it to work (with macros enabled)? 
John_Ru (rep: 4282) Oct 4, '22 at 7:01 am
Hey, John,

I just had the chance to test it, and it works perfectly! Thank you so, so much!
mccoytan (rep: 2) Oct 4, '22 at 8:40 am
Great. Thanks for selecting my Answer, McCoy.
John_Ru (rep: 4282) Oct 4, '22 at 8:51 am
Just one thing McCoy, please consider the code as a kind of special "welcoming gift". In general, the Forum is to answer speciific questions when you get stuck. In this case I've done a mini-project to solve your problem (and hopefully save you lots of time) but that's not normally what happens.

If this has shown you some of the power of VBA, you might consider getting Don's course "Excel VBA Course - From Beginner to Expert". It will teach you a lot (but I get nothing if you do or you don't get it).
John_Ru (rep: 4282) Oct 4, '22 at 8:55 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login