Array formulas are killing my CPU. Is there a better VBA solution?

[USAMax]

I have a array formulas that are killing the CPU.

The arrays are in two columns with 10,000 rows. They are comparing the worksheet with the array formulas with up another worksheet with 65,622 rows. The formulas only use the Min/Max functions.

'THIS RETURNS THE FIRST DATE THAT RELATES TO B2
=MIN(IF($B2='Costing Team (Z9QT05)'!$B:$B,'Costing Team (Z9QT05)'!$G:$G))

'THIS RETURNS THE LAST DATE THAT RELATES TO B2
=MAX(IF($B2='Costing Team (Z9QT05)'!$B:$B,'Costing Team (Z9QT05)'!$H:$H))

Is there a faster VBA code?

 

[Rick Rothstein]

I have a array formulas that are killing the CPU.

The arrays are in two columns with 10,000 rows. They are comparing the worksheet with the array formulas with up another worksheet with 65,622 rows. The formulas only use the Min/Max functions.

'THIS RETURNS THE FIRST DATE THAT RELATES TO B2
=MIN(IF($B2='Costing Team (Z9QT05)'!$B:$B,'Costing Team (Z9QT05)'!$G:$G))

'THIS RETURNS THE LAST DATE THAT RELATES TO B2
=MAX(IF($B2='Costing Team (Z9QT05)'!$B:$B,'Costing Team (Z9QT05)'!$H:$H))

Is there a faster VBA code?

I think your formulas would be more efficient if you restricted ranges for B:B and G:G to the rows where your data was actually located rather than entire columns.

 

[Joe4]

From: https://fastexcel.wordpress.com/201...erences-and-used-range-good-idea-or-bad-idea/

avoid array formula and SUMPRODUCT usage of whole column references.

And from here: https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx#xlArraySumProduct

[FONT=&quot]Do not reference complete rows, or more rows and columns than you need. Array formulas are forced to calculate all the cell references in the formula even if the cells are empty or unused. With 1 million rows available starting in Excel 2007, an array formula that references a whole column is extremely slow to calculate.[/FONT]

So at the very least, if you replace the whole column references in your formula with defined ranges, you should improve performance.

 

[USAMax]

I agree but the values it is looking up are part numbers so I don't know how I could do that plus we would like to be able to resort the data and that would screw up that idea.

Do you agree?

 

[Joe4]

I am not sure I understand. Looking up part numbers and/or sorting the data wouldn't change the number of rows of data.

 

[USAMax]

Hey Joe,

Thank you. This has given me a few things to try.

Much appreciated!

 

[USAMax]

I'm sorry Joe,

I was responding to Rick's post where he said to restrict the range. I assume he was referring to the same thing as you, restrict it to the range and not the entire row.

 

[Joe4]

You mean entire column. Instead of using a reference like:

[COLOR=#333333]'Costing Team (Z9QT05)'!$B:$B

you would want to use:
[/COLOR]

[COLOR=#333333]'Costing Team (Z9QT05)'!$B1:$B65622[/COLOR]

 

[Aladin Akyurek]

1. Try to replace an array-processing formula by an equivalent range-processing formula. For example, you can replace a MIN(IF formula with a MINIFS formula if you have MINIFS on your system.

2. Use dynamic named ranges instead of whole columns or very large definite ranges if the data is changing frequently.

 

[Peter_SSs]

Is there a faster VBA code?

Do the results need to be dynamic? That is, are the values in B2 or 'Costing Team (Z9QT05)' columns B, G, H regularly changing and you need the result of this formula to be immediately updated? If you were happy to have the results populated by vba and for them to then be static or only updated when you want them to be, then your workbook would likely be more responsive most of the time. If you are interested in pursuing this, tell us what sheet and what cells those given formulas are in and what other cells they are currently copied to. Is it down to the end of the data in column B?