Solved

Prioritizing selection criteria

Posted on 2011-03-17
2
246 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:pwflexner
  • 2
2 Comments
 
LVL 12

Expert Comment

by:telyni19
Comment Utility
Would it work for your purposes to use an autofilter to sort the items and then take the top ones based on the quantity requested? If so, in the Method 1 column put a 1 for H, 2 for M, and 3 for L, then sort first by Value and then by Method 1 to get the Method 1 ordering. (The actual Method 1 rankings could be entered in numerical order vertically at this point.) Similarly, for Method 2, put 1 for H or M and 2 for L, then sort by Value then by Method 2.

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.
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 125 total points
Comment Utility
Also, what you can calculate easily is the numerical grade rank, which may help.

For Method 1:
=IF(B6="H",1,IF(B6="M",2,3))

For Method 2:
=IF(OR(B6="H",B6="M"),1,2)

Then sort as I specified above, value first then method rank, and the items will be in the correct order for the chosen method.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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 will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 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

10 Experts available now in Live!

Get 1:1 Help Now