Speed up max if array

**reddwarf** · 03-01-2011, 04:19 PM

I have inherited a workbook which uses an array to look up the max value in a column.
It works well but it crashes when I extend the formula to 25000 rows!
Can someone recommend a faster solution?
From Internet research, SUMPRODUCT maybe the answer but I haven't a clue how to implement it.

Sample workbook attached

Many thanks

**sailepaty** · 03-01-2011, 05:12 PM

Is your data always short by ID?

**Colin Legg** · 03-01-2011, 05:18 PM

Hi,

SUMPRODUCT() won't help here.

Create a pivot table to summarise Max of Values for each ID. That summary may be good enough for you (depending on what you want to do). If you want to put that MAX information back into column C then let us know - it's quite straightforward.

**reddwarf** · 03-01-2011, 05:46 PM

Thanks for the reply Colin,
At least now I know SUMPRODUCT is a dead duck in this case.
Interesting approach on the Pivot Table angle - I would like to explore that to see if it would suit. Can you suggest how to get the max information back into Col C?

Cheers

**Colin Legg** · 03-01-2011, 06:06 PM

Hi,

There are a couple of ways to get it back into column C, but I was hoping that a summary table alone would be sufficient. Out of interest, is there a particular reason you need to put the max back in your 25,000 row table?

Honestly, I haven't tested the efficiency of the different approaches, but the first effort I would make would be to use the GETPIVOTDATA() function. I'll be interested to hear how it performs for you on that large data set.

Once your pivot table is set up to return the Max of Value for each unique ID (row field), the formula in column C2 would be something like:

Please Login or Register  to view this content.

Where G5 is any cell in the pivot table. I hope it performs better than the array formula.

**reddwarf** · 03-02-2011, 05:10 AM

Hi,
Yes, the max value must go back into the data table. The sample I provided was a very simplistic approach of the original. I'll try to explain the setup. The max value represents a code given to equipment, which is used to allocate a score. Code 1 =30pts, Code 2 = 15 pts and code 3=5pts. An applicant can have many pieces of equipment but if he/she has multiple codes then the highest code is always returned which awards the lowest score. The calculation of the score is VERY important. The array formula starts to bomb out after about 6,000 rows where it becomes noticably slow until eventually it crashes when I hit the max size of my data at 25,000 rows.

I will in work in the next 2 hour and will look at the pivot table option. I will feedback on performance.

Cheers

**Colin Legg** · 03-02-2011, 05:36 AM

Hi,

If it performs reasonably then you can further optimise it if your IDs are sorted in column A (as shown in the attachment).

This formula:

Please Login or Register  to view this content.

would become:

Please Login or Register  to view this content.

This effectively means that GETPIVOTDATA() is only called once per ID.

**reddwarf** · 03-02-2011, 06:20 AM

This is excellent Colin,
The Pivot Table option gave me instant results, woo hoo!! Unbelievably fast!!
Not totally au fait with returning the max value back to my sheet.
Sheet1 is my pivot table with all my max values. Equipment worksheet contains all my data. I need to return the data back to Col L2 on the Equipment worksheet but showing the max value against every row where ID occurs, if you know what I mean.

ID Value Max Value
1 3 3
1 1 3
2 1 2
2 2 2

Does the Pivot Table and data have to be on the same worksheet for this to work?

Thanks for your interest and help

**Colin Legg** · 03-02-2011, 07:46 AM

Hi,

This is excellent Colin,
The Pivot Table option gave me instant results, woo hoo!! Unbelievably fast!!

Great, I suspected it would be pretty fast.

Does the Pivot Table and data have to be on the same worksheet for this to work?

No, you can have the pivot table on a different sheet. I've amended the previous example and attached it to this post to show this.

**reddwarf** · 03-02-2011, 08:20 AM

Absoultely brilliant Colin,
You are a life saver - you have saved me days of work. Also, I have learnt something along the way.
Thanks a lot.

BTW, added to your rep :-) - well deserved