Index Match to translate to VBA

[GonzoB]

Hi Helpers,

I have an index-match formula to program in VBA:
=INDEX(Export!$B$2:$H$1000,MATCH(1,(Export!$B$2=B2)*(Export!$H$2="US"),0),5)

I have two sheets in my excel, Export and Result. On tab Result I have a column (B2) with values which need to match with the same type of values from tab Export. This is the first criteria in the match formula. The second criteria is simple, the country need to be US.

I have 170 000 records on tab Export so I'm trying to find the most effective way to program this in VBA. I'm planning to turn off screen updating, automated calculations and events in the macro code. After browsing on forums the conclusion I came to is that in the VBA I need to use Listobjects to bring the data into the memory, instead of using the data on the Sheets. I was trying to program this but I'm new to VBA and got nowhere.

Can somebody help me with the script which does the work? It would be extremely helpful.
Thanks in advance.

 

[Alex Blakenburg]

Hi Alex,

thanks for the code. I tried it and it does not populate the target field on the Results tab. Do you know why?

For j = 1 To UBound(aryResult, 1)
If Dic.exists(aryResult(j, 1)) Then
aryResult(j, 2) = Dic(aryResult(j, 1))
Else
aryResult(j, 2) = ""
End If
Next j

aryResult = rngResult.Value

Your last line is not the last line I supplied. My last line was:-
rngResult = aryResult

 

[GonzoB]

Your last line is not the last line I supplied. My last line was:-
rngResult = aryResult

Indeed. Now I have the same code what you have sent. The target cells are still left empty. When I change this part of the code:

Else
aryResult(j, 2) = ""
End If

to this:

Else
aryResult(j, 2) = "X"
End If

My target cells are getting populated with X, so the issue must be that there are not matches found between Export sheet column B and Result sheet column B.
These columns are storing numbers. On Export the same numbers appear multiple times. On sheet Results there's no duplication, just distinct values.
I tried changing how the values are stored, first as string, then as number, but non of them made the code work.

Any idea?

Thanks

 

[Alex Blakenburg]

Can. You provide xl2bb of both sheets please

 

[Alex Blakenburg]

My target cells are getting populated with X, so the issue must be that there are not matches found between Export sheet column B and Result sheet column B.
These columns are storing numbers

I tried changing how the values are stored, first as string, then as number, but non of them made the code work.

Your comments indicate data mismatch is likely. If you 2nd statement above means you just changed the format that won't fix it.

I have modified the code below to use CStr for the key value on both sheets and should work even with a mismatch.
Please copy in the whole code as is.

Sub LookupValue()

    Dim shtExport As Worksheet
    Dim shtResult As Worksheet
    Dim aryExport As Variant
    Dim aryResult As Variant
    Dim rngResult As Range
    Dim i As Long, j As Long
    Dim strCountry As String
   
    strCountry = "US"

    Set shtExport = Worksheets("Export")
    Set shtResult = Worksheets("Result")
   
    aryExport = shtExport.Range("B2:H" & shtExport.Cells(Rows.Count, "B").End(xlUp).Row).Value
    Set rngResult = shtResult.Range("B2:C" & shtResult.Cells(Rows.Count, "B").End(xlUp).Row)
    aryResult = rngResult.Value
   
    Dim Dic As Object
    Set Dic = CreateObject("Scripting.dictionary")
    For i = 1 To UBound(aryExport, 1)
        If aryExport(i, 7) = strCountry Then
              Dic(CStr(aryExport(i, 1))) = aryExport(i, 5)
        End If
    Next i
   
    For j = 1 To UBound(aryResult, 1)
        If Dic.exists(CStr(aryResult(j, 1))) Then
            aryResult(j, 2) = Dic(CStr(aryResult(j, 1)))
        Else
            aryResult(j, 2) = ""
        End If
    Next j
   
    rngResult = aryResult
 
End Sub

RE: Duplicates

On Export the same numbers appear multiple times. On sheet Results there's no duplication, just distinct values.

If the duplication means the number / US country combination is duplicated, then the code will only pick up the last value. If this is not what you want then you need to specify what you want to do ie use the 1st time it find the combination or the last time or whatever other logic you want to use. I the number is duplicated but the combination (number / country) is not then there is no issue.

 

[GonzoB]

Hi Alex,

thanks for the additional posts. I could make your first code work, the problem was with my excel spreadsheet which was changing during the time I was testing the code. The case is solved now.

p.s.: the spreadsheet I run the code against is 175K rows strong. The code you shared I use to populate 9 different fields. The conditions are similar for each field, only the value to match (strCountry) and the value to return from tab Export changes. Running the code against few thousand or tens of thousands of records is ok. However running the code against the entire database doesn't work, I had few attempts, but after Excel not responding for 3-4 hours I gave it up. This is of course not your fault and I really appreciate your help (and the help of other commenters)!

If you have some idea how to speed up the calculation process, please let me know. In any case this is not crucial as I have written a code which - although slowly - but does the job. It runs in 30 minutes. It looks like this:


Sub lookup()

Dim lookupVal_1 As Range, myString_1 As Variant, Rng_1$
On Error Resume Next
Set lookupVal_1 = Sheets("Result").Cells(i + 2, 14)
Rng_1 = Range(Cells(2, 1), Cells(ExportRow, 3)).Address
myString_1 = WorksheetFunction.VLookup(lookupVal_1, Sheets("Export").Range(Rng_1), 3, False)
If Err.Number > 0 Then
Sheets("ZTT").Cells(i + 2, 3) = vbNullString
Err.Clear
Else
Sheets("ZTT").Cells(i + 2, 3) = myString_1

End If

End Sub


the lookup value is a field which contains a concatenated value (country & B2). This value I generate on both sheets with another VBA Sub.


Cheers

 

[Alex Blakenburg]

If you have some idea how to speed up the calculation process, please let me know. In any case this is not crucial as I have written a code which - although slowly - but does the job. It runs in 30 minutes. It looks like this:

the lookup value is a field which contains a concatenated value (country & B2). This value I generate on both sheets with another VBA Sub.

Using the dictionary should have been the fastest way to achieve what you are trying to do.
You need to help us to help you though.

1) If your results sheet has the lookup value and the country code and you want to look up on both then we can concatenate the lookup string in the dictionary as well. We just need to know what columns to use in Result (If there are more conditions we need those too)
2) We need some sample data from both the Result sheet and the Export sheet in XL2BB or if you can't instal XL2BB on a work computer then via a share site such as drop box, Onedrive, google drive etc.
3) What the results should look like eg which fields you want returned from Export in which columns in Result.

PS: If you are posting code please use the VBA code button.