Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

How do I calculate the max from two arrays?

Hello

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

ScreenshotExercise.xlsx
0
Massimo Scola
Asked:
Massimo Scola
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
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
0
 
Frank WhiteCommented:
Does =MAX(A2:C99)

or =MAX(Date:Shop)

not provide the results you're looking for for some reason?
0
 
Massimo ScolaAuthor 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.
0
Industry Leaders: 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!

 
Massimo ScolaAuthor Commented:
@franker ... It doesn't work because I want to retrieve the name of the shop ..
0
 
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.
0
 
Massimo ScolaAuthor 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?
0
 
Rory ArchibaldCommented:
In G2:

=INDEX(Shop,MATCH(MAX(FREQUENCY(IF(Customer=F2,MATCH(Shop,Shop,0)),ROW(Shop)-ROW($B$2)+1)),FREQUENCY(IF(Customer=F2,MATCH(Shop,Shop,0)),ROW(Shop)-ROW($B$2)+1),0))

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.
Exercise.xlsx
0
 
barry houdiniCommented:
You could also try this version (also an array formula) in G2 copied down

=INDEX(Shop,MODE(IF(Customer=F2,IF(Shop<>"",MATCH(Shop,Shop,0)*{1,1}))))

see attached

regards, barry
mode.xlsx
0
 
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.
0
 
Rory ArchibaldCommented:
Barry,

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

Massimo
0

Featured Post

Industry Leaders: 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!

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now