Rank Cells Alphabetically, Multiple Criteria and Seperate Segments

[willmana]

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	CCC	Authorised	3
Fiona	DDD	Un-Authorised	2
David	EEE	Authorised	2
Fiona	FFF	Un-Authorised	3
Fiona	GGG	Authorised	5
Bianca	HHH	Un-Authorised	1
Fiona	III	Un-Authorised	4

<tbody>
</tbody>

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

Thanks in advance.

 

[oldbrewer]

please define rank - is it by number of companies per name

you can sort by name alphabetically and then company alphabetically and then by status, but I suspect that is not what you want.

 

[willmana]

Hi Oldbrewer,

The ranks is not counting the number of companies or anything like that, I'm just after something which can place the rows in order from A-Z, first by Sales Person, then by Company.

My plan is to transfer this data onto another tab using these numbers.


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

<tbody>
</tbody>

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

<tbody>
</tbody>

Thanks

 

[oldbrewer]

data,sort and choose your fields in desired order - very easy

but you havenot sorted by person as bianca occurs twice

oops you sorted by auhorised etc first