Get top 5 rows of data based on multiple criteria in Excel 2003

[GoingUp]

I have an excel spreadsheet with around 250 rows of data. People are in column C, sales opportunities are in column I and value is in column H.

What I'm trying to do is put an area in the sheet where I have a dynamic dropdown where you can change the person's name and get a dynamic listing of their top opportunities.

I know how to do the drop down and all, what I can't seem to find an answer on, is what kind of formula to use, or if its even possible.

Basically, with random data, I want this. Give me the top 5 active sales opportunities for person X. I have active sales currently classified as a 1 or 0 based on the type, with 1 being an active opportunity.

I then want the output in 5 rows of cells in descending order of opportunity value so that I can reference it else where in my sheet.

Is this even possible at all? Thanks! 🙂

 

[NeoV]

use a filter to select the salesperson

before you do that, however, sort the entire listing in descending order by opportunity value

then, when you select a salesperson, their opps will be in the order you want - also put a filter on the active/inactive field

use an advanced filter to play with it some more

 

[GoingUp]

Originally posted by: NeoV
use a filter to select the salesperson

before you do that, however, sort the entire listing in descending order by opportunity value

then, when you select a salesperson, their opps will be in the order you want - also put a filter on the active/inactive field

use an advanced filter to play with it some more

That still won't do what I want it to. I want the same rows of data to show up everytime regardless where they are in the list of data. This solution also has to work without filtering.

 

[Nothinman]

Where are you getting the data from? If it's from SQL you can just use an 'order by' and 'top' in the statement to get the top X entries.

 

[Buddyd]

You could do an "on worksheet cell change" macro

To help with the macro I would need to know a little more about how to determine the top opportunity. Is it just an a column labled opportunity with a simple 1 to x ranking or is it text?

If you can't use a macro then you best bet will be an index match function for each of the columns you want to bring in given a fixed opportunity ranking. Forgot to mention the index match will only work if the names are sorted.

if your data is unsorted than a macro is really your best option.

 

[PowerEngineer]

With the warning that I often approach Excel problems from odd angles, here's how I'd attack it.

I assume that you're using a drop-down to select the saleman's name, so that you therefore have index in a cell somewhere that is pointing to the row that the selected name appears in (call in iName) in an array of names (call it NameList). I'm also going to assume that it's the name (rather than the name index) that appears in column C.

I'd want to add a new column (say column Z) to the 250 rows. I'll input a formula (in Z1 and then copy down):

IF(MATCH(C1,NameList,0)=iName,1,0)*I1*H1

Values in this column should be zero unless the sale is "active" and the salesman's name has been selected, in which case it will be your value (H1).

I'd then use the LARGE function to find the five highest values in column Z (if there are identical values (in column H) this might not work...). You can match each of the five values back against column Z to get the row where each resides (assuming none are identical). You should then be able to build your table of "top five" opportunities.

Hope this helps...