Rank Cells Alphabetically, Multiple Criteria and Seperate Segments

I have a set of data which I need to attach a fairly complex rank to. I have the headings: Sales Person, Company and Status. Firstly I need to separate the ranking by the different statuses, then rank first by Sales Person then Company. All are text values so will need to be ranked in alphabetical order.

For Example:

Sales Person Company Status Rank
David AAA Authorised 1
Fiona BBB Authorised 4
Bianca GGG Authorised 3
Fiona CCC Un-Authorised 2
David FFF Authorised 2
Fiona DDD Un-Authorised 3
Fiona III Authorised 5
Bianca HHH Un-Authorised 1
Fiona EEE Un-Authorised 4

Does anyone have experience with this sort of thing? I would really appreciate any assistance at all.

Thanks in advance.

Hi wenqq3,

Sorting won't really work for what I'm trying to do here. I really need a rank functions so I can transfer the data onto another tab.

I'm after something which can place the rows in order from A-Z, first by Sales Person, then by Company.



I just realised I mixed up my example it should be as follows:

Sales Person Company Status Rank
David AAA Authorised 2
Fiona BBB Authorised 4
Bianca CCC Authorised 1
Fiona DDD Un-Authorised 2
David EEE Authorised 3
Fiona FFF Un-Authorised 3
Fiona GGG Authorised 5
Bianca HHH Un-Authorised 1
Fiona III Un-Authorised 4


So looking at it in order it would look like:

Sales Person Company Status Rank
Bianca CCC Authorised 1
David AAA Authorised 2
David EEE Authorised 3
Fiona BBB Authorised 4
Fiona GGG Authorised 5
Bianca HHH Un-Authorised 1
Fiona DDD Un-Authorised 2
Fiona FFF Un-Authorised 3
Fiona III Un-Authorised 4

Thanks

Example Template.xlsx

Hi Wenqq3

Yes I'm sorry for the misunderstanding, I've attached a slightly more complex example of what I am after.

I would like to have a data input sheet where I can put all information, then have the spreadsheet automatically break this information out onto one of three separate worksheets in alphabetical order, based on the status.

The best way I could think of was to rank them, but if you have another suggestion I am very open to suggestions.

Thanks,
Will

Example Template.xlsx

Hi Wenqq3,

Thanks so much for your suggestion, it was 99% there but I still had a few unique requirements which meant I couldn't sort.

Anyway I figured out a formula which suits my needs.

If I type the following into cell D2, I get what I need.

SUMPRODUCT(--($C$2:$C$24=C2),--($B$2:$B$24<B2))+SUMPRODUCT(--($C$2:$C$24=C2),--($B$2:$B$24=B2),--($A$2:$A$24<A2))+1

Thanks again for all your help.