Increase macro speed for a Fuzzy Vlookup search

gailb
2StarLounger
Posts: 168
Joined: 09 May 2020, 14:00

Increase macro speed for a Fuzzy Vlookup search

Post by 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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 72002
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Increase macro speed for a Fuzzy Vlookup search

Post by 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.
Regards,
Hans

gailb
2StarLounger
Posts: 168
Joined: 09 May 2020, 14:00

Re: Increase macro speed for a Fuzzy Vlookup search

Post by 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

User avatar
HansV
Administrator
Posts: 72002
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Increase macro speed for a Fuzzy Vlookup search

Post by 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.
Regards,
Hans

gailb
2StarLounger
Posts: 168
Joined: 09 May 2020, 14:00

Re: Increase macro speed for a Fuzzy Vlookup search

Post by gailb »

Okay, no worries.