Index and Match array in VBA

[Lefemmenikita]

Hi

I am dealing with thousands of rows of data.

I am trying to use the multiple criteria index and match in a VBA formula to return a result.

The formula I am currently using is:

Selection.FormulaArray ="=IF(RC[-3]=""Billable"",INDEX(Coding!C1:C5,MATCH(1,(Coding!C1=Data!RC11)*(Coding!C2=Data!RC1),0),3),INDEX(Coding!C1:C5,MATCH(1,(Coding!C1=Data!RC11)*(Coding!C2=Data!RC1),0),4))"
    Range("P2").Select
   
    Columns("P:P").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

This runs out of memory before cycling through all the cells in the column and returning a result

My question is:

Is there a way to make an index/match formula use a dynamic column reference? (therefore, eliminating the need to have an "if" statement in the above array formula)?

Also, is there a way to have an array index/match formula execute via VBA without running out of memory?

I am able to do this outside of VBA, though it takes around 5 mins for Excel to finish calculating the result before I am able to replace the formula with the calculated value.


Hope my question makes sense

Thanks

 

[MUKESHY12390]

can you tell what you are trying to achieve with Array formula?

Selection.FormulaArray


here "selection" represent which range.?

 

[offthelip]

Since what you appear to finally want is the values in the cells and not the complex formula , I would suggest that instead of using index and match, you do the whole thing in VBA by using variant arrays instead, It would be much more flexible and much faster. To give you an idea about how to do that have look at this thread, I know it is about vlookup , but the principle is the same:
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[Lefemmenikita]

can you tell what you are trying to achieve with Array formula?

Selection.FormulaArray


here "selection" represent which range.?

My thinking:
Once the formula has calculated the result, the selection was trying to copy and paste special the data as values so that it would be less of a drain on memory

 

[Lefemmenikita]

Since what you appear to finally want is the values in the cells and not the complex formula , I would suggest that instead of using index and match, you do the whole thing in VBA by using variant arrays instead, It would be much more flexible and much faster. To give you an idea about how to do that have look at this thread, I know it is about vlookup , but the principle is the same:
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

Thanks for the suggestion. Going through that thread, I am not sure I understand how to adapt the code in that to my formula/code.

Specifically, the look up is using 3 criteria on the active sheet ('Data'), with the lookup range being on another sheet('coding')

I can't tell where to alter the vlookup code for this?

 

[Lefemmenikita]

can you tell what you are trying to achieve with Array formula?

Selection.FormulaArray


here "selection" represent which range.?

Sorry, I didn't read your question carefully.


What I am trying to do with the array is return a look up result based on 3 criteria

It is a lookup of codes based on an 'account code' and 'account description' (this is in the match part of the formula).

The column to look up depends on whether something is considered 'billable' (column 3) or 'non-billable' (column 4)

The array formula is entered in column P which is then turned into a value (i.e. a copy and paste special value) once the result is calculated

 

[offthelip]

Thanks for the suggestion. Going through that thread, I am not sure I understand how to adapt the code in that to my formula/code.

Specifically, the look up is using 3 criteria on the active sheet ('Data'), with the lookup range being on another sheet('coding')

I can't tell where to alter the vlookup code for this?

I have written some code to show you how to solve your problem using variant arrays. I had to make a number of assumptions about what your original code was trying to do which I have annotated in the code.

Sub TEST()

With Worksheets("Data")
' I assume:
'AcountDescription in column A
'Acount code in column K
' Billable value in column C
' Nonbillable Value in column D
'
 lastdata = .Cells(Rows.Count, "A").End(xlUp).Row
 datar = Range(.Cells(1, 1), .Cells(lastdata, 11))
End With


With Worksheets("Coding")
' I assume:
'AcountDescription in column B
'Acount code in column A
' Billable/Nonbillable  in column M
' the result in column P
'
 
 Lastcode = .Cells(Rows.Count, "A").End(xlUp).Row
 coder = Range(Cells(1, 1), Cells(Lastcode, 13))
 Range(Cells(1, 16), Cells(Lastcode, 16)) = "Not found"
 Results = Range(Cells(1, 16), Cells(Lastcode, 16))
' I assume there is a header row so I start on row 2
For i = 2 To Lastcode
   For j = 2 To lastdata
    If coder(i, 1) = datar(j, 11) And coder(i, 2) = datar(j, 1) Then
     ' code and description match so check billable
      If coder(i, 13) = "Billable" Then
       Results(i, 1) = datar(j, 3)
      Else
       Results(i, 1) = datar(j, 4)
      End If
     Exit For
    End If
   Next j
 Next i
' write the results out
 Range(Cells(1, 16), Cells(Lastcode, 16)) = Results
 End With
 
End Sub

This code should work very rapidly even if there 100000 rows, since it only accesses the worksheet 7 times in total, it does everything in memory

 

[Lefemmenikita]

Thanks so much Offthelip for all your help. I really appreciate it.

I tested it with the code though realise that I should have been clearer of which sheet contained the lookup and which contained the original data set.

Apologies for that.

I have now uploaded the sample data on Onedrive under:
https://1drv.ms/x/s!AnDRSxsPRV2gpQhb-AoEkFgzQKfO

A screencap of this:

The tab called 'data' is the one which has thousands of rows while 'coding' is what the array formula is looking up

Which part of the code would I need to change to account for this?

Thanks again

 

[offthelip]

here you are try this:

Sub TEST()

With Worksheets("Coding")
' I assume:
'AcountDescription in column B
'Acount code in column A
' Billable value in column C
' Nonbillable Value in column D
 
 Lastcode = .Cells(Rows.Count, "A").End(xlUp).Row
 Coder = Range(Cells(1, 1), Cells(Lastcode, 4))
End With
With Worksheets("Data")
' I assume:
'AcountDescription in column A
'Acount code in column K
' Billable/Nonbillable  in column M
' the result in column P


 lastdata = .Cells(Rows.Count, "A").End(xlUp).Row
 Datar = Range(.Cells(1, 1), .Cells(lastdata, 13))
 
 Range(Cells(2, 16), Cells(Lastcode, 16)) = "Not found"
 Results = Range(Cells(1, 16), Cells(Lastcode, 16))


' I assume there is a header row so I start on row 2
For j = 2 To lastdata
  For i = 2 To Lastcode
    If Coder(i, 1) = Datar(j, 11) And Coder(i, 2) = Datar(j, 1) Then
     ' code and description match so check billable
      If Datar(j, 13) = "Billable" Then
       Results(j, 1) = Coder(i, 3)
      Else
       Results(j, 1) = Coder(i, 4)
      End If
     Exit For
    End If
   Next i
 Next j
' write the results out
 Range(.Cells(1, 16), .Cells(Lastcode, 16)) = Results
End With
 
End Sub

Note I also spotted an error i had made by not putting the dots in when I wrote the variable "Results" back to the worksheet,

 

[Lefemmenikita]

Thank you Offthelip.

I will try this and let you know how I go