Array Formula - Slowing down Excel to a crawl

[ttopsout72]

Good afternoon,

I have an array formula setup to bring back the earliest sell date of a product from a different worksheet in the same workbook. The formula works, but it drags Excel to a massive crawl and sometimes the program freezes up because it's currently 137,051 rows of data. Note: The formula being used also brings back a date of 6/10/2018 if there's an error (no earlier sell date). If I don't enter the formula below as an array, then it just brings back the date of 1/0/1900.

=IFERROR(MIN(IF('Date Identifier'!$A:$A='20 Week Sales Report'!$A2,'Date Identifier'!$K:$K)),"6/10/2018")

How can I convert this array formula into a regular formula in order to get rid of Excel dropping to a crawl?

Thanks for the help!

 

[Nine Zero]

just a thought ... try adding the row number into the formula instead of the entire column

so do something like this

=IFERROR(MIN(IF('Date Identifier'!$A2:$A137051='20 Week Sales Report'!$A2,'Date Identifier'!$K2:$K137051)),"6/10/2018")

Usually Array formulas slowdown when you look at the entire column instead of the used space

 

[Eric W]

Welcome to the board.

Nine Zero is correct, typically array functions are very slow when using whole column references. If you know the bottom row, you can use it in the formula for a definite improvement. If you don't know the exact ending row, you can try 200000 or some such row, which is still better than the 1 million rows+ that is a whole column.

If you have a version of Excel with MINIFS, you can try:

=IFERROR(1/(1/MINIFS('Date Identifier'!$K:$K,'Date Identifier'!$A:$A,'20 Week Sales Report'!$A2)),"6/10/2018")

The "IFS" functions (SUMIFS, COUNTIFS, etc.) are aware of the last row used so using a whole column reference is OK.

 

[Aladin Akyurek]

The formula refers to whole columns, which should be avoided for reasons of efficiency.

Define Lrow in the Name Manager as referring to:

=MATCH(9.99999999999999E+307,'Date Identifier'!$K:$K)

Define Dates in the Name Manager as referring to:

='Date Identifier'!$K$2:INDEX('Date Identifier'!$K:$K,Lrow)

Define Products in the Name Manager as referring to:

='Date Identifier'!$A$2:INDEX('Date Identifier'!$A:$A,Lrow)

With these definitions in place...

Option 1. If MINIFS is available on your system, just enter:

=IF(COUNTIFS(Products,'20 Week Sales Report'!$A2),MINIFS(Dates,Products,'20 Week Sales Report'!$A2),"6/10/2018"+0)

Option 2. Control+shift+enter, not just enter:

=IF(COUNTIFS(Products,'20 Week Sales Report'!$A2),MIN(IF(Products='20 Week Sales Report'!$A2,Dates)),"6/10/2018"+0)

 

[Aladin Akyurek]

And Option 3. Just enter:

=IF(COUNTIFS(Products,'20 Week Sales Report'!$A2),AGGREGATE(15,6,Dates/(Products='20 Week Sales Report'!$A2),1),"6/10/2018"+0)

 

[ttopsout72]

I tried the solution that also before posting on here and the result wasn't any different. Thanks though!

 

[ttopsout72]

Thanks Eric W!!! This worked like a champ the first time. You just made my weekend!

 

[Aladin Akyurek]

I tried the solution that also before posting on here and the result wasn't any different. Thanks though!

Which solution did you try?

 

[ttopsout72]

Which solution did you try?

This was the working formula. It did all the calculations in about 15 seconds....


=IFERROR(MIN(IF('Date Identifier'!$A2:$A137051='20 Week Sales Report'!$A2,'Date Identifier'!$K2:$K137051)),"6/10/2018")

 

[Aladin Akyurek]

This was the working formula. It did all the calculations in about 15 seconds....


=IFERROR(MIN(IF('Date Identifier'!$A2:$A137051='20 Week Sales Report'!$A2,'Date Identifier'!$K2:$K137051)),"6/10/2018")

Have you tried the other suggestions? How did they compare?