Finding Max/Min Without Using Array

Question

I have a Summary tab in an Excel file that needs to look at an Accruals tab to find a Max and Min. The problem I am having is that there are many different Location/Commodity combinations and I need to find the Max and Min based on the Location/Commodity combination. Location and Commodity are in two separate columns.

For example, location 123 can have a commodity of 01, 02, and 03. Location 123 Commodity 01 can have several different prices. I need to look up location 123, commodity 01 and find the max of those prices.

I have been asked not to use an array as it slows the files down. I was previously attempting to use =MAX(IF()) but that was not preferred.

How can I find values on the Accruals tab based on the location column AND the commodity column matching from the Summary tab and then get the max of those values based on the loc/comm combination? Is there a more simple way through VBA? I was trying to write a loop but I am not very advanced and having some difficulty.

Sounds more like a use-case for pivot tables (where you can group your data in various ways then apply summary functions such as max and min to the resulting groups) than for VBA — John Coleman
– John Coleman, Commented Jun 27, 2019 at 15:19
If you have Office 365 or Excel 2019+, you can use the MAXIFS function — tigeravatar
– tigeravatar, Commented Jun 27, 2019 at 15:20
It would be interesting to know how large ranges are in your data and what exact array you had in mind. Using whole columns slow down things a LOT! There are ways to minimize this by using dynamic ranges to make it perfectly workable. — JvdV
– JvdV, Commented Jun 27, 2019 at 15:38
Import the data into an actual database (or does it come from one?), and make a view / write SQL to perform the aggregates. Or bring it into a PowerQuery data model. This is a problem you need to address as a data set, not as individual records. VBA would be terribly inefficient for this. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 27, 2019 at 16:03

twils14 · Accepted Answer · 2019-06-27 16:19:38Z

2

I hadn't really thought about a pivot table. My prior experience with those haven't been great, but this seems to be able to give me exactly what I need.

I created two pivot tables - one for max, one for min and then used the GETPIVOTDATA() function on the Summary tab to pull in those values based on location and commodity combination.

Thank you all for your help!

answered Jun 27, 2019 at 16:19

twils14

457 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

Consider importing the source of these pivots into "Get & Transform" / PowerQuery - you can use it to make a refreshable summary that computes both min and max aggregates, rather easily.

Collectives™ on Stack Overflow

Finding Max/Min Without Using Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related