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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sales Person
[/TD]
[TD]Company
[/TD]
[TD]Status
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD]David
[/TD]
[TD]AAA
[/TD]
[TD]Authorised
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]BBB
[/TD]
[TD]Authorised
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Bianca
[/TD]
[TD]CCC
[/TD]
[TD]Authorised
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]DDD
[/TD]
[TD]Un-Authorised
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]David
[/TD]
[TD]EEE
[/TD]
[TD]Authorised
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]FFF
[/TD]
[TD]Un-Authorised
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]GGG
[/TD]
[TD]Authorised
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Bianca
[/TD]
[TD]HHH
[/TD]
[TD]Un-Authorised
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]III
[/TD]
[TD]Un-Authorised
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Sales Person
[/TD]
[TD]Company
[/TD]
[TD]Status[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]David
[/TD]
[TD]AAA
[/TD]
[TD]Authorised[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]BBB
[/TD]
[TD]Authorised[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Bianca
[/TD]
[TD]CCC
[/TD]
[TD]Authorised[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Fiona[/TD]
[TD]DDD
[/TD]
[TD]Un-Authorised[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]EEE
[/TD]
[TD]Authorised[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Fiona[/TD]
[TD]FFF[/TD]
[TD]Un-Authorised[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Fiona[/TD]
[TD]GGG
[/TD]
[TD]Authorised
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Bianca
[/TD]
[TD]HHH
[/TD]
[TD]Un-Authorised[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Fiona[/TD]
[TD]III[/TD]
[TD]Un-Authorised
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]


So looking at it in order it would look like:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Sales Person
[/TD]
[TD]Company
[/TD]
[TD]Status[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Bianca
[/TD]
[TD]CCC
[/TD]
[TD]Authorised
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]David
[/TD]
[TD]AAA
[/TD]
[TD]Authorised
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]David
[/TD]
[TD]EEE
[/TD]
[TD]Authorised
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]BBB
[/TD]
[TD]Authorised
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]GGG
[/TD]
[TD]Authorised
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Bianca
[/TD]
[TD]HHH
[/TD]
[TD]Un-Authorised
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]DDD
[/TD]
[TD]Un-Authorised
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]FFF
[/TD]
[TD]Un-Authorised
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Fiona
[/TD]
[TD]III
[/TD]
[TD]Un-Authorised
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

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