pwflexner
asked on
Prioritizing selection criteria
In column A are a list of objects. In column B is a letter corresponding to each object ("H" for high, "M", for medium, "L" for low) and in column C is a numerical value for each object. I need to be able to select a number of objects from the group (based on the number entered in cell F1) based on both of the following:
Method 1: Highest rank is for objects with an "H" in column B, then objects with an "M", then objects with an "L". Among each group ("H"s, "M"s, or "L"s), highest rank is for objects with the highest value in column C.
Method 2: All objects are ranked according to column C, but any object with an "L" in column B moves to the bottom of the rankings (if multiple objects have an "L" they are ranked by column B values).
I've attached a spreadsheet as an example.
Prioritizing-Selection-Criteria.xlsx
Method 1: Highest rank is for objects with an "H" in column B, then objects with an "M", then objects with an "L". Among each group ("H"s, "M"s, or "L"s), highest rank is for objects with the highest value in column C.
Method 2: All objects are ranked according to column C, but any object with an "L" in column B moves to the bottom of the rankings (if multiple objects have an "L" they are ranked by column B values).
I've attached a spreadsheet as an example.
Prioritizing-Selection-Criteria.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The problem with calculating a numerical rank in item order is that the calculation would have to take into account how many items there are; essentially you would have to do a sort each time, or search for the highest item, then the next highest unranked item, and so forth, none of which can be done with a simple calculation.
Assuming the items are original in object name order, you could do this fairly efficiently with VBA code by creating an autofilter, filling in the auxiliary columns and performing the sort as I described above, filling down the "calculated" rank in numerical order, then re-sorting by object name and removing the autofilter. If this is an acceptable way to do this, I could expand on my answer by putting together a VBA routine to do it.