dictionary lookup with multiple criteria

[anaysha]

Hi Team, I working with lakhs of row and apply Index match function with multiple criteria basis appliable date of particular name, which will take much time while processing, can you please help me out to get the VBA dictionary lookup with multiple criteria which give result in minimal time, so that I can utlize my time further for anlysing of data.

Below is the table where I need output:

With Criteria of Column A matched with Data Base file and Column C range >= in data base file column C and Column D<= in data base file column D

Data Base:

 

[Anthony47]

I beg your pardon, but I didn't understand which are the information that you type in the first table (sheet Output) and which information you would like to retrieve from the second table; could you be more explicit?
How many Rows & Columns are involved?
If you use a formula, or a macro, could you show the code?

 

[anaysha]

Hi Anthony,

thanks for the reply, actually I am dealing more than >5 Lakh rows and where we need to fetch the information from "Main Data" (consider Data Tab in attached screen shot) and put the same into Main data (Consider as Output Tab), I created lot of formula like Xlookup() with Match() and Indext() with match() etc. along with array... but that all formula taking lot of time...and while exploring on google, I got reference of dictionary lookup where fetching and putting of information with minimal second...

So I need help to provide me dictionary lookup with array to fetch the information...in output tab with considering of field "Name", "Class", "Fees Start" & "Fees end" only when all field match than "Fees" file of respective row information pick from "Data" Tab.


Thanks,
Anaysha

 

[montecarlo2012]

Yes, you can use a dictionary lookup with arrays to fetch information from your "Main Data" and populate it into the "Output" tab. Here's an example code that should work for your situation:

Sub DictionaryLookup()
    Dim dataSheet As Worksheet
    Dim outputSheet As Worksheet
    Dim dataRange As Range
    Dim outputRange As Range
    Dim dataArr As Variant
    Dim outputArr As Variant
    Dim dict As Object
    Dim i As Long
    
    ' Set the data and output sheets
    Set dataSheet = ThisWorkbook.Worksheets("Data")
    Set outputSheet = ThisWorkbook.Worksheets("Output")
    
    ' Get the data and output ranges
    Set dataRange = dataSheet.Range("A2:D" & dataSheet.Cells(dataSheet.Rows.Count, "A").End(xlUp).Row)
    Set outputRange = outputSheet.Range("A2:E" & outputSheet.Cells(outputSheet.Rows.Count, "A").End(xlUp).Row)
    
    ' Convert the data and output ranges to arrays
    dataArr = dataRange.Value
    outputArr = outputRange.Value
    
    ' Create a dictionary to store the data
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Loop through the data array and add the data to the dictionary
    For i = 1 To UBound(dataArr, 1)
        ' Get the key for the dictionary (Name, Class, Fees Start, Fees End)
        Dim key As String
        key = dataArr(i, 1) & "|" & dataArr(i, 2) & "|" & dataArr(i, 3) & "|" & dataArr(i, 4)
        
        ' Add the fees to the dictionary for the key
        dict(key) = dataArr(i, 5)
    Next i
    
    ' Loop through the output array and update the fees column with the dictionary lookup
    For i = 1 To UBound(outputArr, 1)
        ' Get the key for the dictionary lookup
        Dim lookupKey As String
        lookupKey = outputArr(i, 1) & "|" & outputArr(i, 2) & "|" & outputArr(i, 3) & "|" & outputArr(i, 4)
        
        ' Lookup the fees in the dictionary and update the output array
        If dict.exists(lookupKey) Then
            outputArr(i, 5) = dict(lookupKey)
        Else
            outputArr(i, 5) = ""
        End If
    Next i
    
    ' Update the output range with the updated output array
    outputRange.Value = outputArr
End Sub

This code uses a dictionary to store the data from the "Main Data" tab, using the "Name", "Class", "Fees Start", and "Fees End" columns as the key, and the "Fees" column as the value. It then loops through the "Output" tab, looks up the fees in the dictionary using the same key, and updates the "Fees" column in the output array. Finally, it updates the "Output" range with the updated output array.

Note that this code assumes that the "Main Data" and "Output" tabs have headers in the first row, and that the data ranges do not include any empty rows or columns within the range. If your ranges do include empty rows or columns, you may need to modify the code to handle these cases.

 

[anaysha]

Hi Montecarlo, thanks for you reply, I really apricated.....but this code will only work where exact condition matched but if the date is with in the range of particular row there result would not be picked....

I need those code where if date of particular Name is fall under with in then result also populate... like Name "A" date in output sheet is different from Data even then result should be populated of 100 in output tab.

Thanks,
Anaysha

 

[Anthony47]

Could you please tell us how many rows are in your database? Could you please give us a fair view of the database and sample of data we can use for our testing? (either using the XL2BB addin or by sharing a sample workbook)?

In the situation shown in the following example, which should be the returned fee and why?

MULTI_C30319.xlsm
	A	B	C	D	E	F
1			Database
2	Name	Class	Fee Starts	Fee Ends	Fee
3	A	1	2-mar-23	24-nov-23	100
4	A	1	25-nov-23	30-mar-24	150
5
6
7
8
9			Output
10	Name	Class	Fee Starts	Fee Ends	Fee
11	A	1	1-apr-23	31-dic-23	???
12
DEMO

 

[anaysha]

Hi Anthony, thanks for the reply, My database sheet cover more than 5 lakh rows and output near about 1.5 lakh rows .

in the above screen shot Fee ends always with in range...e.g. if database tab "A" Fee start date is 2 mar 23 and output sheet fee start date would be greater or equal to 2 Mar'23 in the same way fee end date in data base is 24 Nov 23 in that case output sheet fees always less than or equal to 24 Nov 23....there after result would be populated.

 

[Anthony47]

Assuming your data and your output table looks like your first message, what if you use in Output!E5 the following formula

=FILTER(Data!$E$3:$E$10000,(Data!$C$3:$C$10000<=C5)*(Data!$D$3:$D$10000>=D5))

Adapt those "10000" to your unknown number of rows

How does it perform?

 

[anaysha]

Assuming your data and your output table looks like your first message, what if you use in Output!E5 the following formula

=FILTER(Data!$E$3:$E$10000,(Data!$C$3:$C$10000<=C5)*(Data!$D$3:$D$10000>=D5))

Adapt those "10000" to your unknown number of rows

How does it perform?

This is really helpful, but filter function will populate #SPILL if Name is repeated along with other criteria if matched in Output sheet... instead of result of all those lines...

e.g. if Name "A" is mentioned tow or more times in database file and we need output of Name "A" in output tab than result basis "Filter" function would be #Spill instead of final result as mentioned in "Fees" filed irrespective of is repetitive.

Thanks,
Anaysha

 

[Anthony47]

My previous formula also lacked checking for Name & Class.
Try this revised one:

=LET(RRes,FILTER(Data!$E$3:$E$10000,(Data!$C$3:$C$10000<=C5)*(Data!$D$3:$D$10000>=D5)*(Data!$A$3:$A$10000=A5)*(Data!$B$3:$B$10000=B5)),INDEX(RRes,ROWS(RRes)))

This will return the last match in your database