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

Matt
A formula can't sort a range of data. There are only 2 ways of doing
what you want, manually and by VBA. What constrains you from using a macro?
Also, Excel can sort a range in only numerical or alphabetical order. It
cannot sort by rank. However, there is a way around that. You can assign a
number or a letter to every rank and VBA (a macro again) can sort by those
values in the background and the end-product will be sorted by rank. Please
post back, we'd like to help you. HTH Otto

"Matt" wrote in message
...
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