Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How do I calculate the max from two arrays?

Posted on 2012-03-22
Medium Priority
Last Modified: 2012-03-23

I'm doing some Excel exercises and I have to find out, with a formula or VBA, the maximum value from two arrays. It sounds easy but it isn't ..

I need Excel to calculate a customer's favourite shop. I have three columns: Date, customer name and shop. Each column is a named range, thus an array (am I right?).

I don't know where to start, I've been looking at this spreadsheet for almost one hour and it is giving me a headache.  It should be possible to solve this, shouldn't it?

Thanks for your help.


Question by:Massimo Scola
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 37752799
If the order of the data in your array is not important, try performing a bubble-sort on each array, then pull the last element (if you sorted ascending) or the first element (if you sorted descending).

example or bubble-sorting an array

Expert Comment

by:Frank White
ID: 37752877
Does =MAX(A2:C99)

or =MAX(Date:Shop)

not provide the results you're looking for for some reason?

Author Comment

by:Massimo Scola
ID: 37752889
I'm not sure whether I will get the desired result... I would like to display which shop a customer has visited the most.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Massimo Scola
ID: 37752913
@franker ... It doesn't work because I want to retrieve the name of the shop ..

Assisted Solution

by:Frank White
Frank White earned 600 total points
ID: 37752964
Yeah, sorry. I think my brain went on strike when I was posting that - I assumed from the question title you wanted to find the largest value in any of the cells, rather than count the instances of something.

Looking at the actual problem again, my first thought would be to use =DCOUNTA() once for each customer/shop pair and return the max of those results, but that could become tricky on the long run as more and more data comes in.

A better solution is to have a UDF that looks for a specific name and counts how many instances of each shop it finds for that customer, then returns the one with the highest count.

However, ideally, I would use a macro to iterate over the dataset (AKA the table on the left) and populate an internal list of customers and shops, counting instances of each shop for each customer, then once it reaches the end of the dataset returning or displaying or writing the list of customers and the highest-counted shop for each. This could possibly be done with an array-entered UDF, but I tend to shy away from those for performance and code maintenance/readability reasons for tasks like this

Using a macro that populates its internal data dynamically seems more solid for me in the long run, and allows you to not worry about how many customers or shops there are, and makes adding new customers or shops to the list very easy: You simply add them to the left table / dataset, and the macro automatically adjusts accordingly, without having to regularly check for new names or shops and add them to the report table or add new rows or do other stuff manually.

Author Comment

by:Massimo Scola
ID: 37753153
what do you mean by internal  list?
do you mean I should create an array with vba and fill the data with all the names?
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 600 total points
ID: 37753260
In G2:


array-entered with Ctrl+Shift+Enter and then fill down.

See attached.

Note: in the event of a tie, this gives the first match of the max value.
LVL 50

Accepted Solution

barry houdini earned 800 total points
ID: 37753469
You could also try this version (also an array formula) in G2 copied down


see attached

regards, barry

Expert Comment

by:Frank White
ID: 37753508
Mind = Blown

rorya's array formulas keep impressing me.

And yes, I meant an array variable (or a Collection object, if you don't mind it being a bit slower and eating up a bit more memory). Out of personal preference, my favored solution would either be a single string array with all the data being fed in each string delimited by spaces (e.g. "CustomerName ShopA 5 ShopB 22 ShopC 8") and parsed as-needed with temporary variants (using VBA.Split()), which would still eat up quite a bit of memory with large datasets, or one string array for customers, another for shops, both unique-valued, and using a multidimensional integer array to keep count.

However, a good alternative would be to simply use some simpler method to find all unique customer names and list them on the right, and use rorya's or barry's array formula to do the actual counting/comparing.
LVL 85

Expert Comment

by:Rory Archibald
ID: 37753664

Very clever - I like it! :)

Author Closing Comment

by:Massimo Scola
ID: 37757114
Hello guys

I have to admit that I'm astounded how much you guys know about Excel.
Although I'm still a beginner compared to you guys, I can sometimes use the formulae in my other projects or exercises I do at school. The more I look at them, the more I understand how/why you wrote them that way.  

Thanks and have a nice day


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question