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:

 

[anaysha]

Thanks for reply, can you please let me know what is RRes stand for here in this formula.

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

 

[Anthony47]

RRes is a "label" and in my own terminology stays for RawResult, and is populated by the records that match the criterias; then INDEX will output only the last of the RawResult.

Remember the syntax for LET:

=LET(Label1,ValueForLabel1,Label2,[ValueForLabel2, [ValueForLabel3, ...]],  CalculatedOutput)

Items between square brackets are optional

 

[anaysha]

RRes is a "label" and in my own terminology stays for RawResult, and is populated by the records that match the criterias; then INDEX will output only the last of the RawResult.

Remember the syntax for LET:

=LET(Label1,ValueForLabel1,Label2,[ValueForLabel2, [ValueForLabel3, ...]],  CalculatedOutput)

Items between square brackets are optional

Hi Anthony, thanks for the function, but i have gone thru with this, but this also taking much time while applying into my sheet 1.5 Lakh rows.

can you please help me on VBA code which will provide output with minimal second.

 

[Anthony47]

can you please help me on VBA code which will provide output with minimal second.

Maybe, but I would prefer not spendig my time to recreate a list of data that already exists; could you share a sample form your DATA sheet?

 

[Anthony47]

Also, could you explain how much is one Lakh?

Edit: forget the question, if it is 100thousands

 

[Anthony47]

In my poor test worksheet I calculated 50 outputs out a database long 150thousand lines in less than 1 second
This using XL365 32 bits over a laptop 4 years old, cpu I7, ram 16 GBytes

How is your real environment? Which are your result?

 

[anaysha]

Also, could you explain how much is one Lakh?

Edit: forget the question, if it is 100thousands

Hi Anthony, Sorry for late reply, I was stuck in some other task, formula which you have share is taking much time with 1 lakh rows and when we applying filter for our analysis our system got hanged.

can you please suggest how could attached my example excel so that you could see my real pain while working on >5 lakhs row.

also one more thing Filter function can found in new version of excel but older version it will show error.

Regards,
Rohit Singh

 

[anaysha]

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.

Hi Montecarlo, can you please support to provide the modify version of above script, as per my requirement, it would be great and it will be really helpful to me.

Thanks,
Anaysha

 

[Anthony47]

Waiting for you feedback I had run some tests; I got the best result with the following macro:

Sub WFee()
Dim myTim As Single
Dim DataR As Range, QueryR As Range, OutR As Range
Dim wOne, wTwo, oArr(), I As Long, J As Long
'
Set DataR = Sheets("Data").Range("A3")          '<<< Starting cell for data in sheet DATA
Set QueryR = Sheets("OutSh").Range("A5")        '<<< Starting point for data in sheet Output
Set OutR = Sheets("OutSh").Range("F5")          '<<< Starting point for the Result
'
Set DataR = Range(DataR, DataR.End(xlDown)).Resize(, 5)
Set QueryR = Range(QueryR, QueryR.End(xlDown)).Resize(, 5)
myTim = Timer
wOne = DataR.Value
wTwo = QueryR.Value
ReDim oArr(1 To UBound(wTwo), 1 To 1)
For I = 1 To UBound(wTwo)
    For J = UBound(wOne) To 1 Step -1
        If UCase(wTwo(I, 1)) = UCase(wOne(J, 1)) And UCase(wTwo(I, 2)) = UCase(wOne(J, 2)) Then
            If wTwo(I, 3) >= wOne(J, 3) And wTwo(I, 4) <= wOne(J, 4) Then
                oArr(I, 1) = wOne(J, 5)
                Exit For
            End If
        End If
    Next J
Next I
OutR.Resize(UBound(oArr) + 5, 1).ClearContents
OutR.Resize(UBound(oArr), 1).Value = oArr
Debug.Print Timer - myTim
End Sub

You have to customize the lines marked <<<, according the comment
It doesn't use features available only on newer Office versions.

Dictionary deliver very good results when the access key if known, whereas in this case we have greater than and lower than criterias; in my tests I got disappointing results

If the above macro doesn't perform, then please share a fair sample of your database; for example you may upload a sample file using a filesharing service (dropbox, onedrive or googledrive, for example); make sure that there is a fair mix of Names, Classes, Start and End time

 

[anaysha]

Waiting for you feedback I had run some tests; I got the best result with the following macro:

Sub WFee()
Dim myTim As Single
Dim DataR As Range, QueryR As Range, OutR As Range
Dim wOne, wTwo, oArr(), I As Long, J As Long
'
Set DataR = Sheets("Data").Range("A3")          '<<< Starting cell for data in sheet DATA
Set QueryR = Sheets("OutSh").Range("A5")        '<<< Starting point for data in sheet Output
Set OutR = Sheets("OutSh").Range("F5")          '<<< Starting point for the Result
'
Set DataR = Range(DataR, DataR.End(xlDown)).Resize(, 5)
Set QueryR = Range(QueryR, QueryR.End(xlDown)).Resize(, 5)
myTim = Timer
wOne = DataR.Value
wTwo = QueryR.Value
ReDim oArr(1 To UBound(wTwo), 1 To 1)
For I = 1 To UBound(wTwo)
    For J = UBound(wOne) To 1 Step -1
        If UCase(wTwo(I, 1)) = UCase(wOne(J, 1)) And UCase(wTwo(I, 2)) = UCase(wOne(J, 2)) Then
            If wTwo(I, 3) >= wOne(J, 3) And wTwo(I, 4) <= wOne(J, 4) Then
                oArr(I, 1) = wOne(J, 5)
                Exit For
            End If
        End If
    Next J
Next I
OutR.Resize(UBound(oArr) + 5, 1).ClearContents
OutR.Resize(UBound(oArr), 1).Value = oArr
Debug.Print Timer - myTim
End Sub

You have to customize the lines marked <<<, according the comment
It doesn't use features available only on newer Office versions.

Dictionary deliver very good results when the access key if known, whereas in this case we have greater than and lower than criterias; in my tests I got disappointing results

If the above macro doesn't perform, then please share a fair sample of your database; for example you may upload a sample file using a filesharing service (dropbox, onedrive or googledrive, for example); make sure that there is a fair mix of Names, Classes, Start and End time

Hi Anthony, thanks for your help, above script perfectly fine and output is given as i actually required, but only problem is when i loaded more data in output sheet still taking more time.

please refer google drive for data: Google Drive: Sign-in