Solved

How do I calculate the max from two arrays?

Posted on 2012-03-22
11
300 Views
Last Modified: 2012-03-23
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
Comment
Question by:mscola
  • 4
  • 3
  • 2
  • +2
11 Comments
 
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
0
 
LVL 3

Expert Comment

by:DaFranker
ID: 37752877
Does =MAX(A2:C99)

or =MAX(Date:Shop)

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

Author Comment

by:mscola
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.
0
 

Author Comment

by:mscola
ID: 37752913
@franker ... It doesn't work because I want to retrieve the name of the shop ..
0
 
LVL 3

Assisted Solution

by:DaFranker
DaFranker earned 150 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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:mscola
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?
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 150 total points
ID: 37753260
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 200 total points
ID: 37753469
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
 
LVL 3

Expert Comment

by:DaFranker
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37753664
Barry,

Very clever - I like it! :)
0
 

Author Closing Comment

by:mscola
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

Massimo
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

943 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now