Index Match 2 criteria with row >1K using VBA

[CHHIEW]

Hi,

I have a big data about 1000+ rows to look up data from another tabsheet using index match with two unique criteria (code & date), however, the speed becomes very slow and excel keep calculating from x% to 100% (status bar)

Example, cell from L3 to AM9 is the index match formula with criteria (code & date), is there a way to do it using vba and make it faster?

Anyone have done this before with vba and would appreciate your help.

Thank you.

 

[Fazza]

hello
one way. please adjust for your data sheet names (or defined names) & field names
regards, Fazza

Sub test()


    Dim sConn As String
    Dim sSQL As String
    Dim oRS As Object
    
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"


    sSQL = Join$(Array( _
        "UPDATE [SomeTable$] S", _
        "INNER JOIN [LookupList$] L", _
        "ON S.CODE = L.CODE AND S.DateField = L.DateField", _
        "SET S.FieldToUpdate_1 = L.LookupField_1, S.FieldToUpdate_2 = L.LookupField_2, S.FieldToUpdate_3 = L.LookupField_3"), vbCr)
               
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open sSQL, sConn
    Set oRS = Nothing
    
End Sub

 

[CHHIEW]

Thanks Fazza, have tried the code but returned error with "microsoft access database engine could not find.....'', this is the correct excel vba correct?

 

[Fazza]

What version of Excel do you have?
Is the file saved?
What set up do you have - data tables/worksheets, field names, code edits (of the SQL) to suit?

 

[CHHIEW]

Hi, i am using office 2010.

i found this link and it's something what i want to achieve, but how to modify it to my need? i am confused on the column and row setting.

Thanks


https://stackoverflow.com/questions...sing-formula-index-match-in-every-cells-excel

Sub FillData()

For Each cell In Worksheets("Data").Columns(2).Cells

    If cell.Value = "" Then Exit Sub    'stop program if no value
    If WorksheetFunction.IsText(cell.Value) = True Then GoTo line1  'do not perform action if YYYYMM

    Set FindMth = Worksheets("Final").Rows(2).Find(cell.Value)  'Find Month at Final Sheet
    Set FindItem = Worksheets("Final").Columns(1).Find(cell.Offset(0, -1).Value, lookat:=xlWhole)   'Find Item Number at Final Sheet

    If Not FindMth Is Nothing Then
        C = FindMth.Column  'Column Month
    Else        
        If Worksheets("Final").Range("B2").Value <> "" Then
            Worksheets("Final").Range("A2").End(xlToRight).Offset(0, 1).Value = cell.Value
            C = Worksheets("Final").Range("A2").End(xlToRight).Column   'Column Month if B2 not empty
        Else
            Worksheets("Final").Range("B2").Value = cell.Value
            C = 2
        End If
    End If

    If Not FindItem Is Nothing Then
        R = FindItem.Row    'Row Item Number
    Else
        Worksheets("Final").Range("A1").End(xlDown).Offset(1).Value = cell.Offset(0, -1).Value
        R = Worksheets("Final").Range("A1").End(xlDown).Row
    End If

    Worksheets("Final").Cells(R, C).Value = cell.Offset(0, 1).Value 'Assign Order Qty
    Worksheets("Final").Range("B1:" & Cells(1, C).Address).Merge    'Merge YYYYMM cell

line1:
Next

End Sub

 

[Fazza]

hi Chhiew

I'll assume your file is saved. Still wondering about the file set up - data tables/worksheets, field names, code edits (of the SQL) to suit?

Per my signature, more explanation of the set up makes it easier/possible to resolve the question. Such as make it really clear exactly how your file/worksheets/tables/data are set up, the sort of data you have & what you want done. Without that there is little point in me participating further in this thread. If that happens & no-one else answers, suggest you may be best to start a new thread.

regards, Fazza

 

[CHHIEW]

Hi Fazza,
Thanks for your help, but i am not really sure what is the file set up you mentioned, sound technical to me.

Below is file, would appreciate if you can help to check the set up of the file, also the yellow cells are index match formula which is working but i want to use vba approach to do it.

Thanks again.

http://jom360.com/vbapic/1.xlsm