VBA VlookUP on large data set

[nefdog]

I have two table, table1 has over 450 thousand rows that include loan number, error type, and responsible job role. table2 has responsible job roles and the department associated with each respective job role.

I'm looking for a fast performing vba code that would look at the responsible job role on table1 and return the matching department from table2, and the result should be written on table1 as a value, given that the whole reason that I want to do this is because I want to get away from having a table that has over 450k vlookups.

thanks in advance

 

[Jerry Sullivan]

Approximately how many rows of data do you have in table2?

What are the names of the sheets that have table1 and table2?

 

[L. Howard]

Hi there,

If you would post a link to a sample workbook, I would be willing to take a shot at a solution. You cannot attach a workbook here, but a link is okay. You can use one of the link utilities to do so, I use Drop Box, but there are others.

Don't need the entire 450K rows, say 150 or so would be fine. Where rows and columns are exactly as your real workbook, sensitive data dummied down. General format of sheet/s is important.

Re-state what you want to happen and where, referring to specific rows, column and sheets and tables.

Howard

 

[nefdog]

Approximately how many rows of data do you have in table2?

What are the names of the sheets that have table1 and table2?

see the following link for a sample of the tow table (there are two tabs one for each table)

Thanks

 

[nefdog]

sorry forgot the link

https://www.dropbox.com/s/6s04lmfzwtc6j5o/sample data 2015.12.28.xlsx?dl=0

 

[nefdog]

here is a link with a sample of the two tables

https://www.dropbox.com/s/6s04lmfzwtc6j5o/sample data 2015.12.28.xlsx?dl=0

thanks

 

[L. Howard]

Try this in a standard module, run on sheet Data.

The Tables on you sheets are actually Table 2 and Table 3, no Table 1.

25,000 rows took about 1 to 2 seconds in my test.

Howard

Option Explicit

Sub H_Column_VLookUp()

  Dim lRowCount&
  lRowCount = Cells(Rows.Count, "H").End(xlUp).Row
  
  With Range("J2").Resize(lRowCount)
    .Formula = "=IF(ISNA(VLOOKUP(H2,Table3,2,FALSE)),"""",VLOOKUP(H2,Table3,2,FALSE))": .Value = .Value
  End With
  
 
End Sub

 

[nefdog]

WOW!! that works surer fast, thank you so much L. Howard, this is great!!

 

[Jerry Sullivan]

Here's another method using a Scripting Dictionary. It executes in about one third the time of the Vlookup formula approach code in Post #7.

On my PC, For 25K Rows: Vlookup: 0.92 sec / Dictionary: 0.34 sec
For 450K Rows: Vlookup: 12.1 sec / Dictionary: 3.9 sec

Testing at the approximate scale of your dataset is worthwhile (even though it can be a pain to setup), since sometimes the alternative methods can work better than one another at different scales. In this case, the 3:1 ratio is consistent.

This code assumes you've added a Library Reference to MS Scripting Runtime to your VBA Project.

To do that: In the VBA Editor: Tools > References... > scroll down and check the box next to "Microsoft Scripting Runtime" > OK

Sub LookupUsingDictionary()
'--Reads lookup table into dictionary
'  Reads lookup values into array then
'  stores results of dictionary lookups in array
'  then transfers array values to cells in one write

'--requires library reference to MS Scripting Runtime
 Dim dicLookupTable As Scripting.Dictionary
 Dim lNdx As Long, lColCount As Long
 Dim sKey As String
 Dim vLookupValues As Variant
 Dim vLookupTable As Variant

 Set dicLookupTable = New Scripting.Dictionary
 dicLookupTable.CompareMode = vbTextCompare

 vLookupTable = Range("Table3[[Responsible]:[Department]]").Value

 lColCount = UBound(vLookupTable, 2)
 
 For lNdx = LBound(vLookupTable) To UBound(vLookupTable)
   sKey = vLookupTable(lNdx, 1)

   If Not dicLookupTable.Exists(sKey) Then _
      dicLookupTable(sKey) = vLookupTable(lNdx, lColCount)
 Next lNdx
   
 vLookupValues = Range("Table2[Responsible]").Value
   
 For lNdx = LBound(vLookupValues) To UBound(vLookupValues)
   sKey = vLookupValues(lNdx, 1)
   If Len(sKey) Then
      If dicLookupTable.Exists(sKey) Then
         vLookupValues(lNdx, 1) = dicLookupTable(sKey)
      Else
         vLookupValues(lNdx, 1) = vbNullString
      End If
   End If
 Next lNdx
  
 Range("Table2[Department]").Value = vLookupValues
  
End Sub

One other difference worth noting is the user of the Table's field names in the references in lieu of hard coding ranges or lookup column references. That allows fields in the table to be added and removed to a certain extent without the need to modify the code.

 

[Peter_SSs]

Similar to Jerry's but I think should be a smidgen faster again as it does a little less testing & does not need to remove any values from the array to be written back to Table2.
I have assumed that Table3 only has 2 columns and the values in the first column of it are unique.
This code also does not require you to set a reference MS Scripting Runtime.

Sub LookupUsingDictionary2()
  Dim d As Object
  Dim i As Long
  Dim DeptList As Variant, RespList As Variant, Results As Variant

  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  DeptList = Range("Table3").Value
  RespList = Range("Table2[Responsible]").Value
  ReDim Results(1 To UBound(RespList), 1 To 1)
  For i = 1 To UBound(DeptList)
    d.Add DeptList(i, 1), DeptList(i, 2)
  Next i
  For i = 1 To UBound(RespList)
      Results(i, 1) = d(RespList(i, 1))
  Next i
  Range("Table2[Department]").Value = Results
End Sub