0

I am automating a report build in Excel using VBA. Part of that process I use vlookup to compare the lists. Tab 1 contains roughly 180,000 line items with the unique ID, the vlookup takes that ID and compares against "owners" in tab 2 with roughly 250,000 line items. Run time on this operation is roughly 25-30mins and I'm wondering if there is a faster way? Maybe I should perform this comparison using a script outside of Excel to reduce calculation time?

It's working fine so I haven't tried to troubleshoot. I have a few ideas around doing the work outside of excel, in the background but looking for ideas from the broader group.

Here is the line I'm using to perform the lookup now, it's repeated 5x in code.

Range("Table").Offset(1).Select
ActiveCell.FormulaR1C1 = "=IFNA(VLOOKUP([@ID],table,2,0),""Unassigned"")"

With each iteration of the above line in the workbook recalculates which is taking the 30mins. I have tried setting calculation to xlManual then back to xlAutomatic, no luck. Was thinking I could just run a single worksheet calc after the formulas where written.

Curious if anyone knows of a faster way to accomplish this. As I said the run time is 30mins for this section, and the total run time is 35-40mins.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Garland Brooks
  • 3
  • 5
  • 2
    Do not use `.Select`: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Scott Craner Jun 13 '19 at 23:54
  • 1
    please post the whole loop and we can help. – Scott Craner Jun 13 '19 at 23:55
  • I would use more VBA and some dictionary other way it would be slow - compare 2 big sets w/o any optimisation must be slow. Common field functions are very slow workarounds. Some hints ex. here https://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure (?) – Jan Jun 14 '19 at 07:30
  • Scott using the .Select offset to get to the first row after the header and enter the formula. I'm not running it in a loop which I could since I have 5 iterations of the same code...might try that, but Ron's answer below cut run time down significantly. Thanks! – Garland Brooks Jun 14 '19 at 22:40

2 Answers2

0

Maybe try to convert the result of your VLOOKUP formula into value after each iteration, something like that:

Sub foo()
    Dim rngCell As Range

    For Each rngCell In Range("Table").Offset(1)
        rngCell.FormulaR1C1 = "=IFNA(VLOOKUP([@ID],table,2,0),""Unassigned"")"
        rngCell.Value = rngCell.Value
    Next rngCell    
End Sub

This should prevent it from recalculating your VLOOKUP results. Alternatively, use INDEX+MATCH combination, or - if your dataset is sorted - use VLOOKUP with match mode TRUE (approximate) instead of FALSE (exact).

Justyna MK
  • 3,523
  • 3
  • 11
  • 25
0

If you can SORT your data, you can build a double VLOOKUP with the range_lookup parameter set to TRUE. This causes VLOOKUP to do a binary search which, on a large DB, may run 100x faster:

=IF(VLOOKUP(ID,Table,1,TRUE)=ID,VLOOKUP(ID,Table,2,TRUE),NA())

And if you are using the VLOOKUP method, you should be sure to turn off ScreenUpdating and also set Calculation to manual while you are populating the worksheet with the formulas.

Alternatively, it might be faster to just read the data into a VBA array or dictionary, and do all your lookup and matching within VBA. Again, if you can sort your list, you can use a binary search which will be much faster.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Binary search option might be a winner, I will work on that. I can sort the data easy enough. Thanks! I also have screenupdating and calcs auto set to off. – Garland Brooks Jun 14 '19 at 16:59
  • Ron that did the trick, first run was down to 2 mins. The lists were already sorted just a matter of doubling up on the lookup. Thanks!! – Garland Brooks Jun 14 '19 at 22:38