I have a master data sheet, with columns of different information.
I'd like to pull information from 3 different columns
(Rank,LastName,Shift) into a separate column (I know how to do this).
What I would then like to have happen is, in another separate column,
have the information sorted alphabetically by last name. There are
duplicate last names, in which case I'd like them sorted in rank
order. Is this at all possible? Also, I can't use a VB Macro - it
needs to be in a formula or array format.

Rank.......LastName.........Shift
PO Smith B/1
BC Jones B/2
FF Smith B/1
LT Harris C/4


What I'd like to end up with, in one column is this:

LT Harris C/4
BC Jones B/2
PO Smith B/1
FF Smith B/1

The rank possibilities in descending order a BC, CT, LT, PO, EVD,
FF, FFPM, FPR