Solved

How do I calculate the max from two arrays?

Posted on 2012-03-22
11
299 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

12 Experts available now in Live!

Get 1:1 Help Now