3
\$\begingroup\$

I have a complex array function that takes way too much time to calculate, which is why I'm looking for ways to make it more efficient. Basically, I have a transaction sheet in which I list all goods sold in a bunch of different stores. The formula that I've written picks the last stock amount in the list where a specific good was sold at a specific store. An index match doesn't work here as I have 2 different row arguments. The formula looks as follows:

{=INDEX($O$20:O57,MAX(IF($K$20:K57=K58,IF($D$20:D57=D58,ROW($O$20:O57)-MIN(ROW($O$20:O57))+1))),1)}

To explain in more detail:

  • In column O is the amount of stock after each transaction listed (of which I need the last entry in the list)
  • In column K is the item code for the specific good
  • In column D is the name of the branch where the good was sold

With increasing transactions, the sheet grows quite substantially and becomes already with ~1,000 rows a time-consuming nightmare. I have written a vba code that copies the entire table except for the last few rows and pastes everything as values back into the table to avoid having too many array formulas in the table, but I can still watch the extended Lord of the Ring cuts while it's computing.

I appreciate any ideas, I'm sure there are ways to make this thing a little faster ;)

\$\endgroup\$
1
  • \$\begingroup\$ Since you're using VBA in your workbook anyway, have you tried to replace the complex array formula with a VBA-coded UDF ? \$\endgroup\$
    – PeterT
    Nov 2 '19 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.