How do I calculate the max from two arrays?


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.


Massimo ScolaInternshipAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
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
Frank WhiteCommented:
Does =MAX(A2:C99)

or =MAX(Date:Shop)

not provide the results you're looking for for some reason?
Massimo ScolaInternshipAuthor Commented:
I'm not sure whether I will get the desired result... I would like to display which shop a customer has visited the most.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Massimo ScolaInternshipAuthor Commented:
@franker ... It doesn't work because I want to retrieve the name of the shop ..
Frank WhiteCommented:
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.
Massimo ScolaInternshipAuthor Commented:
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?
Rory ArchibaldCommented:
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.
barry houdiniCommented:
You could also try this version (also an array formula) in G2 copied down


see attached

regards, barry

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank WhiteCommented:
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.
Rory ArchibaldCommented:

Very clever - I like it! :)
Massimo ScolaInternshipAuthor Commented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.