Way to make list of values in descending order based on criteria


I seem to think there might be a way to search through a list and return the values, in descending order, based on a criteria.  For example, I have a sample list of sales regions (Col A) and employee sales totals (Col B).  I want to search the list for, say, all figures from the West Region.  The first cell would have the largest value (max).  After that I'd have large([list filtered by criteria,2), and so on.  Does anyone know if that's possible?
BBluAsked:
Who is Participating?

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

x
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.

Saqib Husain, SyedEngineerCommented:
The easiest way is to use additional helper columns which would contain the LARGE(list,rank) values and any other columns which would be required. These columns could be used as keys and the sort performed.

If this is not appropriate then upload some sample data and we shal try to help accordingly.

Saqib
0
Jignesh TharSenior ManagerCommented:
BBlu - I suggest using Pivottable and max function to get this list. I have tried doing this in attached excel. Is this what you are looking for?
Region-Sales-Total.xlsx
0
Jignesh TharSenior ManagerCommented:
something like below
jigneshthar-438807.flv
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

BBluAuthor Commented:

No, actually, I was trying to find a tricky way to sort through data and and dynamically create a chart.  For example, if the South only had sales in Jan,Feb,March, and Dec, I want to list the totals for each month in descending order (I could then reverse it with the chart properties).  Anyhow, I'd have a column that started with the 1st largest, then 2nd largest date, etc.  And find the totals based on that date and that region.  So I'd need to look in the whole list, checking column A for the region, then get the largest value (date), then sum the sales for that date.  In the next row, it would still check column A for the region, this time getting the 2nd largest date and getting the sum for its sales.

Attached is the revised sheet.  Sorry about that. Sumproduct-Large2.xlsx
0
Jignesh TharSenior ManagerCommented:
All right. I would think that Pivottable is easiest for this requirement. Can you  review attached and tell if it suffices your need? you can filter specific region / date in Pivottable.

This table is sorted descending and sums sales for particular month and agregate of region
Sumproduct-Large2-Jignesh.xlsx
0
Rory ArchibaldCommented:
0

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
BBluAuthor Commented:
That is EXACTLY what I was looking for.  I knew/thought it might be accomplished with an array formula, but would have never gotten that.  Thanks, Rorya.
0
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.