VBA Code Using Dictionary Search

[Excelpromax123]

Hi guys. I have an excel file with about 60,000 lines, Currently using Vlookup as the file is extremely slow. Thanks to the forum for help with the dictionary code. Search like the image described below. sincerely thank
If using a formula, then at cell G5=IF(F5="","",IFERROR(VLOOKUP(F5,$B$5:$C$60000,2,0),0)) ( 60,000 lines very slow )

VBA VLOOKUP Dictionary .xlsb

drive.google.com

 

[JEC]

Im surprised that the VLOOKUP is slow on 60000 rows. It shouldn't
Alternatively you could use a macro. Only runs when you want to. Keeps your file fast.

Sub jec()
 Dim rng
 Set rng = Range("B5", Range("C" & Rows.Count).End(xlUp))
 With Range("F5", Range("F" & Rows.Count).End(xlUp))
   .Offset(, 1).Value = Evaluate("iferror(vlookup(t(if({1}," & .Address & "))," & rng.Address & ",2,0),0)")
 End With
End Sub

 

[Sequoyah]

Hi everyone,
here is a solution using Dictionary, as requested by Excelpromax123, adapted from my answer in another thread:

Sub Test2()
    
    Dim Rng As Range, Dn As Range, n As Long, Dic As Object, ray As Variant
    
    Application.ScreenUpdating = False
    
    With Sheets(1)
        ray = .Range(.Range("B5"), .Range("C" & Rows.Count).End(xlUp))
    End With
    
    Set Dic = CreateObject("scripting.dictionary")
    
    Dic.CompareMode = 1
    
    For n = 1 To UBound(ray, 1)
        
        If Not Dic.exists(ray(n, 1)) Then Dic(ray(n, 1)) = (n)
        
    Next
    
    With Sheets(1)
        Set Rng = .Range(.Range("F5"), .Range("F" & Rows.Count).End(xlUp))
        
        For Each Dn In Rng
            
            If Dic.exists(Dn.Value) Then
                Dn.Offset(, 1) = ray(Dic(Dn.Value), 2)
            Else
                Dn.Offset(, 1) = 0
            End If
        Next Dn
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[Excelpromax123]

Im surprised that the VLOOKUP is slow on 60000 rows. It shouldn't
Alternatively you could use a macro. Only runs when you want to. Keeps your file fast.

Sub jec()
 Dim rng
 Set rng = Range("B5", Range("C" & Rows.Count).End(xlUp))
 With Range("F5", Range("F" & Rows.Count).End(xlUp))
   .Offset(, 1).Value = Evaluate("iferror(vlookup(t(if({1}," & .Address & "))," & rng.Address & ",2,0),0)")
 End With
End Sub

Your code is too short and runs really fast. Thank you so much
In cell F5 I am using the formula below. Can you help me convert to a similar code?
F5 =SUMPRODUCT(ISNUMBER(SEARCH(""&$B$4:$B$12&"",""&E4&""))*$C$4:$C$12)

 

[Peter_SSs]

In cell F5 I am using the formula below. Can you help me convert to a similar code?
F5 =SUMPRODUCT(ISNUMBER(SEARCH(""&$B$4:$B$12&"",""&E4&""))*$C$4:$C$12)

Duplicate to: Sum Vlookup each small value for 1 string

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.