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.