verying array formula to VBA

[rickp2177]

Hi All,

I have an array formula as below that works as intended, but is really slow to calculate values due to the size of the dataset. Therefore my though to speed this up is to convert it to VBA. I have searched, however am struggling to find any examples online.

Therefore any assistance would be appreciated.

{=INDEX(Sheet1!$A$1:$S$25466,MATCH(1,(Sheet1!$A:$A=B20)*(Sheet1!$D:$D=C20),0),E20)}

The formula is matching an article code with a colour code and then returns the qty from the size field.

B20 = Article Code
C20 = Colour Code
E20 = Size field

Sheet1

REF BRANCH STORES COLOUR 001 002 003 004
123456 BLK 5 4 0 3

Many Thanks

 

[Jonmo1]

You'll see a 'significant' improvement of performance if you remove the Entire Column References A:A and D:D

=INDEX(Sheet1!$A$1:$S$25466,MATCH(1,(Sheet1!$A$1:$A$25466=B20)*(Sheet1!$D$1:$D$25466=C20),0),E20)

 

[Jonmo1]

And I'm not certain this will help performance, but you can make it not require CTRL + SHIFT + ENTER like this

=INDEX(Sheet1!$A$1:$S$25466,MATCH(1,INDEX((Sheet1!$A$1:$A$25466=B20)*(Sheet1!$D$1:$D$25466=C20),0),0),E20)

 

[rickp2177]

Hi Thanks for the reply...

Have just tested and am seeing a 'big improvement'! in speed which is great, however it is still taking around 10 minutes to process. Any thoughts on how it could be improved further as I have large amounts of data to process?

 

[Jonmo1]

Will there be only 1 occurance of any given Article Code + Color Code ?
Which version of Excel are you using?

 

[rickp2177]

Hi Jonmo,

Yes there will only be 1 occurrence of a given Article Code + Color Code

Using Excel 2010

 

[Jonmo1]

Try this.

=SUMIFS(INDEX(Sheet1!$A$1:$S$25466,0,E20),Sheet1!$A$1:$A$25466,B20,Sheet1!$D$1:$D$25466,C20)

 

[rickp2177]

Many Thanks Jonmo, have now completed my data conversion and your tips have saved me hours of processing time!

Could not get the SUMIFS statement to work due to the way the data was structured, but the reworking to remove the need for CTRL + SHIFT + ENTER has simplified things

Rick