• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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?
0
BBlu
Asked:
BBlu
1 Solution
 
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now