VBA code to lookup multiple columns using Dictionary

[RahulBakshi]

VBA code to lookup multiple columns using Dictionary. Image of data attached. Thanks

 

[James006]

Hi,
Have you tried to insert a Pivot Table ?

 

[RahulBakshi]

Thanks James for quick response. No, actually, I am trying to get the result from Dictionary. As dictionaries are too fast when we deal with large dataset. So wanted to get from dictionary

 

[James006]

Agree with you regarding the speed of Dictionary ... but the Pivot Table algorithm is far from being ridiculous ... have a try ...

 

[RahulBakshi]

It will be a new learning experience for me. I am not aware of it. It will be very helpful if you could help me.

 

[James006]

If you want to learn how to use VBA Dictionary:

How to Use VBA Dictionary in Excel (6 Practical Examples)

How to use VBA Dictionary in Excel is done by showing 6 examples: create dictionary, add, retrieve keys and items, check, search, update keys

www.exceldemy.com

 

[Tupe77]

Did you mean something like this?

1. Fu_TS_DictLookup is a workbook function. It's uses Global dict Object to avoid repeatedly reading data from the sheet. (Reason for 2.2)
2. It has two parameters.
2.1 "Emp ID" to Lookup(Single cell or horizontal range).
2.2 ReReadData is optional boolean parameter to forcing rereading data-range.
- Used when the data that is the subject of searches has been changed between searches.
3. There is no error handling or data validations.
4. Data is readed from "Sheet1" Columns A to G Rows 2 to LastRow of Colunm D
5. Dictionary Key is "Emp ID"s from Sheet1, Column D
6. The VBA code must be placed at the top of the module. (for global statement)

My apologies for any quirks, English is not my native language.

Usage:
Single value to Lookup:

=Fu_TS_DictLookup(I2)

multiple values to Lookup:

=Fu_TS_DictLookup(I3:I8)

subject of searches has been changed:

=Fu_TS_DictLookup(I2,TRUE)

To module:

Option Explicit
Global dict As Object
Function Fu_TS_DictLookup(EmpIdRNG As Range, Optional ReReadData As Boolean = False) As Variant()
Dim EmpID As Long
Dim ws As Worksheet: Set ws = Worksheets("Sheet1")

If dict Is Nothing Or ReReadData Then ' Reading Data from Sheet
    Debug.Print "No data on memory or data refresh demanded, reading data"
    
    ' Creating variables
    Set dict = CreateObject("Scripting.Dictionary")
    Dim IdRNG As Range, c As Range
    
    ' Range where to find all "Emp ID"s
    Set IdRNG = ws.Range("D2:D" & ws.Cells(Rows.Count, 4).End(xlUp).Row)
    
    ' Read all data to dictionary
    For Each c In IdRNG
        dict(c.Value) = WorksheetFunction.Transpose(WorksheetFunction.Transpose(c.Offset(0, -3).Resize(1, 7).Value)) ' c.value(add key if missing) = range.values to transform to array
    Next

Else ' Data is allready written to Global dict
    Debug.Print "Data allready in Dictionary"
End If

If EmpIdRNG.Cells.Count > 1 Then
    'Creating variables
    Dim i As Long: i = 1
    Dim FuRows As Long: FuRows = EmpIdRNG.Cells.Count
    Dim RetArrD2 As Variant
    
    ReDim RetArrD2(1 To FuRows, 1 To 5)
    For Each c In EmpIdRNG
        EmpID = c.Value
        RetArrD2(i, 1) = dict(EmpID)(2)
        RetArrD2(i, 2) = dict(EmpID)(3)
        RetArrD2(i, 3) = dict(EmpID)(7)
        RetArrD2(i, 4) = dict(EmpID)(6)
        RetArrD2(i, 5) = dict(EmpID)(5)
        i = i + 1
    Next
    
    ' The return value of Fu_WriteBack is a 2-dimensional RetArrD2 array
    Fu_TS_DictLookup = RetArrD2

Else
    ' Store EmpIdRNG.value in EmpID to avoid repeated reading from sheet
     EmpID = EmpIdRNG.Value
     
    ' Reading the desired data from the Dictionary into the Array
    Dim RetArr(1 To 5) As Variant
    RetArr(1) = dict(EmpID)(2)
    RetArr(2) = dict(EmpID)(3)
    RetArr(3) = dict(EmpID)(7)
    RetArr(4) = dict(EmpID)(6)
    RetArr(5) = dict(EmpID)(5)
    
    ' The return value of Fu_WriteBack is a 1-dimensional RetArr array
    Fu_TS_DictLookup = RetArr
End If

End Function

 

[RahulBakshi]

Thanks, James for sharing the link. But I am looking how to lookup using Dictionary with multiple columns.

 

[RahulBakshi]

Thanks Tupe77, though English is not your native language, but you have clearly explained. You have done exactly, what I am looking for. You made a UDF which I very much helpful. To make it more dynamic, can we make the EmpIDRNG and Sheet1 as parameters of UDF so that the lookup column and Sheet name can be dynamic. Also, can we make the resultant columns also dynamic. For example, if instead of First Name present in Column J, it is present in Column K then also we get the correct result.

 

[Tupe77]

I have to think about it.
1. How does the user select the data area?
2. How is the unique (key) data column selected?
3. How are the return value columns selected and arranged?