Increase macro speed for a Fuzzy Vlookup search

[gailb]

I've used this Fuzzy Vlookup macro many times and it seems to work well; however, it seems to really eat up the resources on the PC when run. Well, at least on my dinosaur PC.

Any thoughts on how this can be made to run quicker? I'm trying to match the name starting in A2 with the list of names in column C. The lookup matches will go in column E with the true or false match in column F. Macro attached in workbook.

Just for testing purposes I've built in only 3 loops.

[HansV]

The FillTable macro took 25 seconds on my PC, so a bit over 8 seconds per run.
The code is too complicated to analyze, so I cannot suggest improvements.
You might try Microsoft's free Fuzzy Lookup Add-In. Since it is compiled, it should be faster. I haven't used it myself.

[gailb]

Hi Hans, yes, this code take about 30 seconds on my computer, but with 4000 rows, this would take more than 20 mins.

Unfortunately, I can't install any add-ins on my work computer, so that won't be an option.

The FillTable macro was just my attempt, but do you have a suggestion on something to replace it that would write the formula in column E in chunks versus all 4000 rows? I started with loops of 100 rows at a time, but this was just testing things out.

I also thought, maybe writing the 100 cells with the formula and then converting those cells to just values would help, but it doesn't appear to make a difference. I downloaded the recalc file from Charles Williams Decision Models and it doesn't appear the FuzzyVLookup is volatile.

I also added turning on/off the screen, but that also only appeared to save a second or so.

Code: Select all

Sub TurnEverythingOff()
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
End Sub

[HansV]

Fuzzy lookup is a process that by necessity requires a very large number of steps. I don't see ways to speed it up, sorry.

[gailb]

Okay, no worries.