Matching optimized for RAM not CPU consumption

Question

I'm looking for different ways to get the row number of an identical cell in another range.

One of the fastest ways is to use the Match function. When run across two very large ranges (upwards of 500,000 rows each), it will (by default) run in parallel across the CPU, and, given a good CPU, go very quickly. When the CPU is weak or has few cores (or doesn't support multi-threading), however, this can take a very long time. It uses almost no RAM.

Is there a match type that uses a lot of RAM but very little CPU? This would be very useful to computers which have a lot of ram, but an older CPU.

Answer 1

I know of 4 ways to search but I think the determining factor will always be the CPU

The test:

- out of the max of 1048576
- find cell        A1048573

Nothing comes close to Match

---

Time -   LookupAppMatch(): 0.047 sec      CPU usage 1%
Time -   LookupAppMatch(): 0.031 sec      CPU usage 0%
Time -   LookupAppMatch(): 0.031 sec      CPU usage 1%

Time -    LookupWSMatch(): 0.047 sec      CPU usage 1%
Time -    LookupWSMatch(): 0.063 sec      CPU usage 0%
Time -    LookupWSMatch(): 0.047 sec      CPU usage 0%

---

Time -       LookupFind(): 0.672 sec      CPU usage 8%
Time -       LookupFind(): 0.625 sec      CPU usage 5%
Time -       LookupFind(): 0.625 sec      CPU usage 7%

Time -    LookupForLoop(): 0.297 sec      CPU usage 5%
Time -    LookupForLoop(): 0.297 sec      CPU usage 3%
Time -    LookupForLoop(): 0.297 sec      CPU usage 3%

Time - LookupAutoFilter(): 0.672 sec      CPU usage 7%
Time - LookupAutoFilter(): 0.359 sec      CPU usage 5%
Time - LookupAutoFilter(): 0.375 sec      CPU usage 7%

---

Code:

---

Option Explicit

Private Const FND = "A1048573"
Private t As Double

Public Sub SetupData()
    Sheet1.Columns(1).Formula = "=Address(Row(), Column(), 4)"
End Sub

Public Sub LookupAppMatch()
    t = Timer
    Sheet1.Cells(1, 2) = Application.Match(FND, Sheet1.Columns(1), 0)
    Debug.Print "Time -   LookupAppMatch(): " & Format(Timer - t, "0.000") & " sec"
End Sub

Public Sub LookupWSMatch()
    t = Timer
    Sheet1.Cells(2, 2) = WorksheetFunction.Match(FND, Sheet1.Columns(1), 0)
    Debug.Print "Time -    LookupWSMatch(): " & Format(Timer - t, "0.000") & " sec"
End Sub

---

Public Sub LookupFind()
    t = Timer
    Sheet1.Cells(3, 2) = Sheet1.Columns(1).Find(What:=FND, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).Row
    Debug.Print "Time -       LookupFind(): " & Format(Timer - t, "0.000") & " sec"
End Sub

Public Sub LookupForLoop()
    Dim ur As Variant, r As Long
    t = Timer
    ur = Sheet1.Columns(1)
    For r = 1 To UBound(ur)
        If ur(r, 1) = FND Then Exit For
    Next
    Sheet1.Cells(4, 2) = r
    Debug.Print "Time -    LookupForLoop(): " & Format(Timer - t, "0.000") & " sec"
End Sub

Public Sub LookupAutoFilter()
    t = Timer
    Application.ScreenUpdating = False
    With Sheet1.Columns(1)
        .AutoFilter Field:=1, Criteria1:=FND
        Sheet1.Cells(5, 2) = .Rows.Count - .SpecialCells(xlVisible).Cells.CountLarge - 1
        .AutoFilter
    End With
    Application.ScreenUpdating = True
    Debug.Print "Time - LookupAutoFilter(): " & Format(Timer - t, "0.000") & " sec"
End Sub

---

More details here and here