Index Match alternative

[aditis]

I have a worksheet which works part on VBA used only for i/p of data to the second sheet which works on formulas, but the macro takes a long time to finish. I've array formulas (index match) on my worksheet which I'm guessing could be a reason for it. I'm looking for an alternative that I could use in VBA to make the process go faster.


the index match code on my worksheet looks something like this

=IFERROR(IFERROR(INDEX('Allowance Pointer'!$I:$I,MATCH(1,('Raw Data'!Q3='Allowance Pointer'!A:A)*('Raw Data'!G3>='Allowance Pointer'!G:G)*('Raw Data'!G3<='Allowance Pointer'!H:H)*('Raw Data'!I3>='Allowance Pointer'!C:C)*('Raw Data'!I3<='Allowance Pointer'!D:D)*('Raw Data'!L3='Allowance Pointer'!E:E)*('Raw Data'!H3='Allowance Pointer'!F:F),0),0),INDEX('Allowance Pointer'!$I:$I,MATCH(1,('Raw Data'!Q3='Allowance Pointer'!A:A)*('Raw Data'!G3>='Allowance Pointer'!G:G)*('Raw Data'!G3<='Allowance Pointer'!H:H)*('Raw Data'!I3>='Allowance Pointer'!C:C)*('Raw Data'!I3<='Allowance Pointer'!D:D)*('Raw Data'!L3='Allowance Pointer'!E:E)*("A"='Allowance Pointer'!F:F),0),0)),0)

 

[NARAYANK991]

Hi Aditi ,


I have no idea how this formula was generated , so I will only comment on another issue :


1. I have reduced the length of the formula , in case anyone else wants to try and investigate it further :


=(INDEX(Tab_A!$I:$I,MATCH(1,(Tab_B!Q3=Tab_A!A:A)*(Tab_B!G3>=Tab_A!G:G)*(Tab_B!G3<=Tab_A!H:H)*(Tab_B!I3>=Tab_A!C:C)*(Tab_B!I3<=Tab_A!D:D)*(Tab_B!L3=Tab_A!E:E)*(Tab_B!H3=Tab_A!F:F),0),0),INDEX(Tab_A!$I:$I,MATCH(1,(Tab_B!Q3=Tab_A!A:A)*(Tab_B!G3>=Tab_A!G:G)*(Tab_B!G3<=Tab_A!H:H)*(Tab_B!I3>=Tab_A!C:C)*(Tab_B!I3<=Tab_A!D:D)*(Tab_B!L3=Tab_A!E:E)*("A"=Tab_A!F:F),0)))


2. In general , it is bad practice , or at the least lazy practice to just use column names in a formula ; using a defined range is good practice , and probably faster.


3. My personal opinion is that I would not try and frame any formula which is in excess of 100 characters , unless it is the only way a problem can be solved. In today's situation , where Excel is so intelligent , and where computing power is so cheaply available , I would use helper columns wherever possible ; this is something like the standard computing technique of divide and conquer ; break up a formula into smaller , more easily digested chunks , so that if any change is required , it can be done more easily , and probably in fewer places. You are using two INDEX statements , within which there are two MATCH statements ; I have not checked it thoroughly , but I think the MATCH statement can be put into a helper column so that the length of the formula you have given reduces. In some cases , using a helper column speeds up Excel's calculation.


4. Asking someone to optimize your formula without any sample data is not really justified ; finding out a syntax error is easy without any data ; but trying to optimize a formula is a different matter ; sometimes , altering the structure of the data slightly can result in a dramatic change / improvement in the formula.


Narayan

 

[Hui]

Aditis


Firstly, Welcome to the Chandoo.org forums.


In addition to Narayan's comments I'd suggest you add the following to your code:

This will stop delays due to screen updating, recalculation which may be forced by use of volatile excel functions

Application.Calculation = xlCalculationManual 'Disable calculations

[code]Application.ScreenUpdating = FALSE 'Disable Screenupdating

[code]Application.EnableEvents = False 'Disable Excel


[code]'Your code here

Application.Calculation = xlCalculationAutomatic[/code] 'Re-enable calculations

Application.EnableEvents = True[/code] 'Re-enable Excel events:

Application.ScreenUpdating = TRUE[/code] 'Re-enable screen updating


Obviously if you need to re-enable calculations inside your code because the code is reliant on intermediate results you need to place the code accordingly

 

[aditis]

Thanks Narayan and Hui. I did shorten the sheet names and put in the stop screen updating in the VBA and it worked!!!!!! thanks so much again....