using VBA for mass lookup and update of cells instead of vlookup / IF statement RRS feed

  • Question

  • Hi

    I have a Master sheet containing ~ 300000+ data rows where one Column L has a numerical 8 digit value; let us say 80818105. The next column, Column K in this sheet is blank and has the cell K1 labelled as "Department Name"

    I have a corresponding mapping file where in column A, i have the 8 digit number and in column B,  there is  text - let us say Engineering Department.   the mapping file has almost 1500 rows specifying 8 digit number on each row and the next column has a department name.

    I do a vlookup between the Master sheet and the mapped data sheet and update department name to the last column that is column K.  this is done for all the 300000 rows.

    Unfortunately Data in the master file gets updated now and then and every time i have to use the vlookup and then  change the formulas to values.

    Question is Can VBA reduce my efforts and if yes can somebody help me with the code.



    Sunday, February 26, 2017 6:34 PM

All replies

  • Try this. Change the sheet names to the actual names in your workbook.

    Sub LookupNames()
        Dim m As Long
        Application.ScreenUpdating = False
        With Worksheets("Master")
            m = .Range("L" & .Rows.Count).End(xlUp).Row
            With .Range("K2:K" & m)
                .FormulaR1C1 = "=IFERROR(VLOOKUP(RC[1],'Mapping'!C1:C2,2,FALSE),"""")"
                .Value = .Value
            End With
        End With
        Application.ScreenUpdating = True
    End Sub

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 26, 2017 8:29 PM
  • This solution uses a data dictionary.  Wonder which is faster?

    Sub FillDeptName()
      Dim ws As Worksheet
      Dim ws2 As Worksheet
      Dim d As New Scripting.Dictionary  ' reference Microsoft Scripting Runtime
      Dim i As long
      Dim key As String
      Dim val As String
      Set ws = Worksheets("Master")
      Set ws2 = Worksheets("Mapping")
      i = 2
      While ws2.Cells(i, 1) <> ""
        key = ws2.Cells(i, 1)
        val = ws2.Cells(i, 2)
        d(key) = val
        i = i + 1
      i = 2
      While ws.Cells(i, 11) <> ""  'col k
        key = ws.Cells(i, 11)
        ws.Cells(i, 12) = d(key)  ' col l
        i = i + 1
    End Sub

    • Edited by mogulman52 Monday, February 27, 2017 12:57 AM
    Monday, February 27, 2017 12:53 AM
  • Is there a way to tweak this formula so it checks another level?

    what i mean is that i have a a source file and a mapping file with policy holder ids and policy numbers. Ideally id like to map just by policy number (and using the above dictionary structure works great to do so). However, the mapping file does not have certain policy numbers so then i was asked to map by policy holder ID. So i want to add another dimension that would check "if policy number does not exist in the mapping file, use the policy holder id to map the account name"

    Monday, July 31, 2017 2:07 PM
  • Hard to answer without a specific example.  You may need 2 dictionaries.  One with policy number and another with policy number Id.  If the policy number does not exist use the other dictionary.  You may be able to use 1 dictionary if policy number and policy number Id are distinct.  If you want a specific example provide a simple test case.
    Monday, July 31, 2017 2:29 PM
  • Policy number is unique however policy holder ID may have different policy numbers, hence the reason why policy number is the better option to map first. 

    Test case 1: 

    Source Data Table

    Policy Holder ID = 890300, Policy Number = 3000135, Account = "UNMAPPED"

    Mapping Data Table

    Policy Holder ID = 890300, Policy Number =        , Account = "COLINSUR"

    After macro is run, Account is updated to a blank value, i.e "UNMAPPED" is deleted, since i am first mapping by policy number. The goal would be for the dictionary to replace it with "COLINSUR"

    Monday, July 31, 2017 2:53 PM
  • i think i got it:

    can you please tell me what you think of this. Dictionary d is the policy number and dictionary e is the policy holder ID.

    Option Explicit

    Sub UpdateName()

    Dim ws As Worksheet: Set ws = Worksheets("Colombia")
    Dim mp As Worksheet: Set mp = Worksheets("Mapping Actuaria")
    Dim d As New Scripting.Dictionary
    Dim e As New Scripting.Dictionary
    Dim i As Long
    Dim key As String
    Dim key2 As String
    Dim val As String
    Dim val2 As String

    Application.ScreenUpdating = False

    i = 2
    While mp.Cells(i, "G") <> ""
        key = mp.Cells(i, "G")
        val = mp.Cells(i, "J")
        d(key) = val
        i = i + 1

    i = 2
    While mp.Cells(i, "E") <> ""
        key2 = mp.Cells(i, "E")
        val2 = mp.Cells(i, "J")
        e(key2) = val2
        i = i + 1

    i = 2
    While ws.Cells(i, "H") <> ""
        key = ws.Cells(i, "H")
        key2 = ws.Cells(i, "F")
        If d.Exists(key) Then
        ws.Cells(i, "O") = d(key)
        ElseIf e.Exists(key2) Then
        ws.Cells(i, "O") = e(key2)
        End If
        i = i + 1

    • Edited by SantiagoA Monday, July 31, 2017 3:44 PM
    Monday, July 31, 2017 3:31 PM
  • At first glance it seems right.  Good work.
    Monday, July 31, 2017 3:55 PM