Brainstorming for Ranking System

[JRT2006]

I have been tasked with creating an Excel sheet to rank employees for annual leave pick priority. The issue I am having is trying to meet specific criteria since we have two groups of individuals, military and civilian, and each team has three subgroups, crew chiefs, driver/operators, and firefighters. I'll explain the process below and see if any of the more proficient excel guru's has a better idea of how to accomplish this.

We are trying to develop a point system for each based on their rank, their SCD date (time in service), and their date of rank (the day they put on their current rank). The system has been created, although we have not agreed on numbers, where the problem comes is if an individual who has double the amount of service (SCD date) gets promoted to the next subgroup, he automatically jumps over individuals who have been in that subgroup for years.

It was proposed to me that we should separate the military and civilians, rank each subgroup in military and civilian based on Date of Rank, then combine military and civilian and rate based on the point system created.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]SCD[/TD]
[TD]DoR[/TD]
[TD]Pts[/TD]
[TD]Pick[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]A.A.[/TD]
[TD]2/1/1995[/TD]
[TD]5/15/2012[/TD]
[TD]7.7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]K.A.[/TD]
[TD]1/30/2000[/TD]
[TD]10/20/2016[/TD]
[TD]6.45[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]D.F.[/TD]
[TD]9/23/1984[/TD]
[TD]11/10/2017[/TD]
[TD]10.29[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]M.C.[/TD]
[TD]3/5/2007[/TD]
[TD]8/26/2013[/TD]
[TD]5.68[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]J.T.[/TD]
[TD]6/10/2008[/TD]
[TD]7/24/2014[/TD]
[TD]5.36[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]J.P.[/TD]
[TD]10/10/2011[/TD]
[TD]5/25/2015[/TD]
[TD]2.52[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]A.R.[/TD]
[TD]3/23/2010[/TD]
[TD]9/1/2015[/TD]
[TD]2.91[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]Z.E.[/TD]
[TD]12/2/2010[/TD]
[TD]11/7/2015[/TD]
[TD]2.74[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]B.A.[/TD]
[TD]2/1/2012[/TD]
[TD]8/25/2017[/TD]
[TD]2.45[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

The table above simulates how the results should return, employees are ranked based on a point system, but if somebody with higher points has a lower date of rank than those with the same rank as him, he should go below them. The reason for this is because he has not spent as much time in the position as the others. "D.F." for example has many years in service (SCD) which increases his overall point value and would normally place him at the very top of the list, but he has only held the position for a week since he was recently promoted. Therefore he is bumped below those with the same rank.

I'm trying to create a fair system to rank everybody based on the number of years they have in service and the amount of time they have at their position. I do not see an option to attach a file or I would attach my current spreadsheet for everyone to mess with.

Any help brainstorming this would be greatly appreciated

 

[Gerald Higgins]

I think the first thing you need to do is work out exactly what policy you want to use, when ranking people.
Excel can't help you produce this policy, but once you've decided what policy to use, you can get Excel to do the calculations or prioritising for you.

It looks, from the table, as if you want to apply a simple policy which can be described as follows:
Sort first by RANK
Sort second by DATE OF RANK

It looks as if you are ignoring SCD for these purposes.
If you do actually want to take SCD into account, can you post some examples showing what difference SCD would make ?

Note - if you do want to sort by RANK, you might need to use a custom sort function within Excel's Data Sort functionality.

 

[JRT2006]

I think the first thing you need to do is work out exactly what policy you want to use, when ranking people.
Excel can't help you produce this policy, but once you've decided what policy to use, you can get Excel to do the calculations or prioritising for you.

It looks, from the table, as if you want to apply a simple policy which can be described as follows:
Sort first by RANK
Sort second by DATE OF RANK

It looks as if you are ignoring SCD for these purposes.
If you do actually want to take SCD into account, can you post some examples showing what difference SCD would make ?

Note - if you do want to sort by RANK, you might need to use a custom sort function within Excel's Data Sort functionality.

Gerald, thanks for the quick reply.

You are close to the policy. The first sort should be by Pts. The second sort should be Date Of Rank.
Using the table attached earlier as an example, every member is sorted by their Points, but because D.F. has a more recent Date of Rank, he falls below those at his position (GS8) and above those with lower points outside of his position (TSgt and SSgt).

The way I have the points is:
1 point for every 4 years of service. If D.F. has been serving since 1984, 33.162 years. At 1 point per 4 years, he would get 8.29 points. K.A., on the other hand, has been serving since 2000, 17.8 years. At 1 point per 4 years, he would get 4.45 points.

There are also points added for rank, TSgt being the highest ranking in the group get an additional 3 points, GS8 gets 2, and SSgt gets 1.

D.F. has a total of 10.29 points
K.A. has a total of 6.45 points.

Typically, this would rank D.F. higher than K.A., but since D.F. has only been in the position for 3 days as opposed to the 1 year that K.A. has been in it for, D.F. should rank below K.A.

I've tried a custom sort, unless I am doing it wrong, it places D.F. below the entire group and not just below those at his rank.

 

[Gerald Higgins]

OK thanks.

You said "The first sort should be by Pts".

But your table suggests this is not the case.

If you actually sorted it in descending order of points, DF would be at the top of the subsection for rank GS8.

But later on, you say that DF should rank below KA. DF will never rank below KA as long as you are sorting on points.

I'm not going to get involved in any discussion about whether the actual points calculations are correct - I assume they are correct and you are happy with the way that works.

What I meant by a Custom Sort, was to sort the Ranks in the first column.
They are not sorted alphabetically, they are sorted by some logic known to you, and I was suggesting using Custom Sort to get all the GS8s together at the top, followed by all the TSgts, followed by all the SSgts, and so on.

Have you actually tried sorting this data, using Excel's Data Sort feature ?
Apply a Custom Sort to the rank column for the first sort key, then sort by DOR for the second sort key, and I think that should give you the results you want.

 

[JRT2006]

I appreciate your help with this.

Sorting the table by rank would definitely get the correct results as far as the top 3 GS8 positions, but sorting the table by rank in either Alphabetical order or reverse would give SSgt priority over TSgt, even though their points are lower.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]SCD[/TD]
[TD]DoR[/TD]
[TD]Pts[/TD]
[TD]Actual Rank[/TD]
[TD]Prefer Rank[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]A.A.[/TD]
[TD]2/1/1995[/TD]
[TD]5/15/2012[/TD]
[TD]7.70[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]K.A.[/TD]
[TD]1/30/2000[/TD]
[TD]10/20/2016[/TD]
[TD]6.45[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]D.F.[/TD]
[TD]9/23/1984[/TD]
[TD]11/10/2017[/TD]
[TD]10.29[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]J.P.[/TD]
[TD]10/10/2011[/TD]
[TD]5/25/2015[/TD]
[TD]2.52[/TD]
[TD]9[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]A.R.[/TD]
[TD]3/23/2010[/TD]
[TD]9/1/2015[/TD]
[TD]2.91[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]Z.E.[/TD]
[TD]12/2/2010[/TD]
[TD]11/7/2015[/TD]
[TD]2.74[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]B.A.[/TD]
[TD]2/1/2012[/TD]
[TD]8/25/2017[/TD]
[TD]2.45[/TD]
[TD]11[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]S.M.[/TD]
[TD]11/20/2011[/TD]
[TD]9/2/2017[/TD]
[TD]2.50[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]E.F.[/TD]
[TD]2/20/2012[/TD]
[TD]10/16/2017[/TD]
[TD]2.43[/TD]
[TD]12[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]M.C.[/TD]
[TD]3/5/2007[/TD]
[TD]8/26/2013[/TD]
[TD]5.68[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]J.T.[/TD]
[TD]6/10/2008[/TD]
[TD]7/24/2014[/TD]
[TD]5.36[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]B.B.[/TD]
[TD]1/10/2008[/TD]
[TD]9/12/2012[/TD]
[TD]7.46[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Those are the results I get using a custom sort, I added an extra column for the results I prefer based on apparently some crazy rules I would like excel to perform:

1. Check for the highest point total.
2. Compare the DoR of the highest point total to employees of the same rank, IF DoR is less than members of the same rank, move to next highest point total.
3. Perform the same action, if the DoR is not less than the employees of the same rank, place a 1 for that employee.
4. Continue through the list until all employees are ranked.

If this is even possible, it would:

1. Locate 10.29 as the highest point total
2. See that the DoR is 11/10/2017 (more recent than other GS8's)
3. Move to the next highest point total
4. See the next highest point total as 7.70
5. See that the DoR is 5/15/2012 (not the most recent of GS8's)
6. Place a 1 in his corresponding column
7. Search for the highest total again
8. Locate 10.29 as the highest point total
9. See that the DoR is 11/10/2017 (more recent than other GS8's)
10. Move to the next highest point total.
11. See the next highest point total as 7.46
12. See that the DoR is 9/12/2012 (not the most recent of TSgt's)
13. Place a 2 in his corresponding column.
14. Etc.

This is probably something that requires code rather than formulas, and if that is the case I need to drop the project altogether.

 

[Gerald Higgins]

In your "Preferred rank" column, do you REALLY want TSgt BB to be 2nd overall ?

If not, please can you post a version of the table showing all entries in exactly the correct sequence you want them to appear in ?

 

[JRT2006]

I do not, that was a mistake on my part as I typed the wrong point total for him, it should have been 5.46 instead of 7.46

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]SCD[/TD]
[TD]DoR[/TD]
[TD]Pts.[/TD]
[TD]Pick[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]A.A.[/TD]
[TD]2/1/1995[/TD]
[TD]5/15/2012[/TD]
[TD]7.70[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]K.A.[/TD]
[TD]1/30/2000[/TD]
[TD]10/20/2016[/TD]
[TD]6.45[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]D.F.[/TD]
[TD]9/23/1984[/TD]
[TD]11/10/2017[/TD]
[TD]10.29[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]B.B.[/TD]
[TD]1/10/2008[/TD]
[TD]9/12/2012[/TD]
[TD]5.46[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]M.C.[/TD]
[TD]3/5/2007[/TD]
[TD]8/26/2013[/TD]
[TD]5.68[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]J.T.[/TD]
[TD]6/10/2008[/TD]
[TD]7/24/2014[/TD]
[TD]5.36[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]J.P.[/TD]
[TD]10/10/2011[/TD]
[TD]5/25/2015[/TD]
[TD]2.53[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]A.R.[/TD]
[TD]3/23/2010[/TD]
[TD]9/1/2015[/TD]
[TD]2.91[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]Z.E.[/TD]
[TD]12/2/2010[/TD]
[TD]11/7/2015[/TD]
[TD]2.74[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]B.A.[/TD]
[TD]2/1/2012[/TD]
[TD]10/16/2017[/TD]
[TD]2.45[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]S.M.[/TD]
[TD]11/20/2011[/TD]
[TD]9/2/2017[/TD]
[TD]2.50[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]E.F.[/TD]
[TD]2/20/2012[/TD]
[TD]10/16/2017[/TD]
[TD]2.43[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

This table would be the preferred output given the point system I have created. These are the steps taken to determine these ranks.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Step 1:[/TD]
[TD]Locate highest point total[/TD]
[TD]10.29[/TD]
[/TR]
[TR]
[TD]Step 2:[/TD]
[TD]Check DoR of 10.29 with matching Ranks[/TD]
[TD]More Recent[/TD]
[/TR]
[TR]
[TD]Step 3:[/TD]
[TD]Proceed to next highest point total[/TD]
[TD]7.70[/TD]
[/TR]
[TR]
[TD]Step 4:[/TD]
[TD]Check DoR of 7.70 with matching Ranks[/TD]
[TD]Not Most Recent[/TD]
[/TR]
[TR]
[TD]Step 5:[/TD]
[TD]Assign "1" to Pick column of 7.70[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Step 6:[/TD]
[TD]Locate highest point total[/TD]
[TD]10.29[/TD]
[/TR]
[TR]
[TD]Step 7:[/TD]
[TD]Check DoR of 10.29 with matching Ranks[/TD]
[TD]More Recent[/TD]
[/TR]
[TR]
[TD]Step 8:[/TD]
[TD]Proceed to next highest point total[/TD]
[TD]6.45[/TD]
[/TR]
[TR]
[TD]Step 9:[/TD]
[TD]Check DoR of 6.45 with matching Ranks[/TD]
[TD]Not Most Recent[/TD]
[/TR]
[TR]
[TD]Step 10:[/TD]
[TD]Assign "2" to Pick column of 6.45[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Step 11:[/TD]
[TD]Locate highest point total[/TD]
[TD]10.29[/TD]
[/TR]
[TR]
[TD]Step 12:[/TD]
[TD]Check DoR of 10.29 with matching Ranks[/TD]
[TD]Last of Rank**[/TD]
[/TR]
[TR]
[TD]Step 13:[/TD]
[TD]Assign "3" to Pick column of 10.29[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Step 14:[/TD]
[TD]Locate highest point total[/TD]
[TD]5.68[/TD]
[/TR]
[TR]
[TD]Step 15:[/TD]
[TD]Check DoR of 5.68 with matching Ranks[/TD]
[TD]More Recent[/TD]
[/TR]
[TR]
[TD]Step 16:[/TD]
[TD]Proceed to next highest point total[/TD]
[TD]5.46[/TD]
[/TR]
[TR]
[TD]Step 17:[/TD]
[TD]Check DoR of 5.46 with matching Ranks[/TD]
[TD]Not Most Recent[/TD]
[/TR]
[TR]
[TD]Step 18:[/TD]
[TD]Assign "4" to Pick column of 5.46[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Step 19:[/TD]
[TD]Locate highest point total[/TD]
[TD]5.68[/TD]
[/TR]
[TR]
[TD]Step 20:[/TD]
[TD]Check DoR of 5.68 with matching Ranks[/TD]
[TD]Not Most Recent[/TD]
[/TR]
[TR]
[TD]Step 21:[/TD]
[TD]Assign "5" to Pick column of 5.68[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


Now let me throw another brief example out there, based on your reply about TSgt "B.B." being placed 2nd, it was a mistake on points, but it could be possible if "B.B" had more service time than what I have listed for him, let's say he joined in 2004 instead of 2008. Based on the point system in place, he would have a 6.46 total instead of a 5.46, see below.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]SCD[/TD]
[TD]DoR[/TD]
[TD]Pts[/TD]
[TD]Pick[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]A.A.[/TD]
[TD]2/1/1995[/TD]
[TD]5/15/2012[/TD]
[TD]7.70[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]B.B.[/TD]
[TD]1/10/2004[/TD]
[TD]9/12/2012[/TD]
[TD]6.46[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]K.A.[/TD]
[TD]1/30/2000[/TD]
[TD]10/20/2016[/TD]
[TD]6.45[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]GS8[/TD]
[TD]D.F.[/TD]
[TD]9/23/1984[/TD]
[TD]11/10/2017[/TD]
[TD]10.29[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]M.C.[/TD]
[TD]3/5/2007[/TD]
[TD]8/26/2013[/TD]
[TD]5.68[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]TSgt[/TD]
[TD]J.T.[/TD]
[TD]6/10/2008[/TD]
[TD]7/24/2014[/TD]
[TD]5.36[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]J.P.[/TD]
[TD]10/10/2011[/TD]
[TD]5/25/2015[/TD]
[TD]2.53[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]A.R.[/TD]
[TD]3/23/2010[/TD]
[TD]9/1/2015[/TD]
[TD]2.91[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]Z.E.[/TD]
[TD]12/2/2010[/TD]
[TD]11/7/2015[/TD]
[TD]2.74[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]B.A.[/TD]
[TD]2/1/2012[/TD]
[TD]10/16/2017[/TD]
[TD]2.45[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]S.M.[/TD]
[TD]11/20/2011[/TD]
[TD]9/2/2017[/TD]
[TD]2.50[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]SSgt[/TD]
[TD]E.F.[/TD]
[TD]2/20/2012[/TD]
[TD]10/16/2017[/TD]
[TD]2.43[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

Following the same steps as above, this is how it would work:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Step 1:[/TD]
[TD]Locate highest point total[/TD]
[TD]10.29[/TD]
[/TR]
[TR]
[TD]Step 2:[/TD]
[TD]Check DoR of 10.29 with matching Ranks[/TD]
[TD]More Recent[/TD]
[/TR]
[TR]
[TD]Step 3:[/TD]
[TD]Proceed to next highest point total[/TD]
[TD]7.70[/TD]
[/TR]
[TR]
[TD]Step 4:[/TD]
[TD]Check DoR of 7.70 with matching Ranks[/TD]
[TD]Not Most Recent[/TD]
[/TR]
[TR]
[TD]Step 5:[/TD]
[TD]Assign "1" to Pick column of 7.70[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Step 6:[/TD]
[TD]Locate highest point total[/TD]
[TD]10.29[/TD]
[/TR]
[TR]
[TD]Step 7:[/TD]
[TD]Check DoR of 10.29 with matching Ranks[/TD]
[TD]More Recent[/TD]
[/TR]
[TR]
[TD]Step 8:[/TD]
[TD]Proceed to next highest point total[/TD]
[TD]6.46[/TD]
[/TR]
[TR]
[TD]Step 9:[/TD]
[TD]Check DoR of 6.46 with matching Ranks[/TD]
[TD]Not Most Recent[/TD]
[/TR]
[TR]
[TD]Step 10:[/TD]
[TD]Assign "2" to Pick column of 6.46[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Step 11:[/TD]
[TD]Locate highest point total[/TD]
[TD]10.29[/TD]
[/TR]
[TR]
[TD]Step 12:[/TD]
[TD]Check DoR of 10.29 with matching Ranks[/TD]
[TD]More Recent[/TD]
[/TR]
[TR]
[TD]Step 13:[/TD]
[TD]Proceed to next highest point total[/TD]
[TD]6.45[/TD]
[/TR]
[TR]
[TD]Step 14:[/TD]
[TD]Check DoR of 6.45 with matching Ranks[/TD]
[TD]Not Most Recent[/TD]
[/TR]
[TR]
[TD]Step 15:[/TD]
[TD]Assign "3" to Pick column of 6.45[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Step 16:[/TD]
[TD]Locate highest point total[/TD]
[TD]10.29[/TD]
[/TR]
[TR]
[TD]Step 17:[/TD]
[TD]Check DoR of 10.29 with matching Ranks[/TD]
[TD]Last of Rank**[/TD]
[/TR]
[TR]
[TD]Step 18:[/TD]
[TD]Assign "4" to Pick column of 10.29[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Step 19:[/TD]
[TD]Locate highest point total[/TD]
[TD]5.68[/TD]
[/TR]
[TR]
[TD]Step 20:[/TD]
[TD]Check DoR of 5.68 with matching Ranks[/TD]
[TD]Not Most Recent[/TD]
[/TR]
[TR]
[TD]Step 21:[/TD]
[TD]Assign "5" to Pick column of 5.68[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


As you can see in that example and the steps that were given, if "B.B." had more time in service, he would just barely beat out "K.A." and is assigned "2" for his pick position. Even though "K.A." has more service time than "B.B." the point system rewards him more points for his rank, because a TSgt is a higher rank than a "GS8".

Sorry for the abundance of tables and information, I just wanted to make sure I got the steps out there. This is to show that TSgt's don't necessarily HAVE TO fall behind GS8's. I did this in an attempt to not confuse you, but I probably made it worse.

 

[Gerald Higgins]

Hi.

Your first table can be replicated by a straightforward Data, Sort, using Rank for the first sort key, based on a Custom List which you will have to set up, and then using DoR for the second sort key.

But if the ranks don't have to be grouped together, I'm sorry but I just don't understand the logic behind what you want to do.

For example, if TSgt BB ranks above GS8 DF, why DON'T the other two TSgts ALSO rank above GS8 DF ?

QUESTION - are you committed to using that specific points system ?
Is it worth considering calculating a different points system that would allocate points in such a way that you could then use that to sort your data ?

 

[JRT2006]

This will be a long post, so bear with me. I'll give a little background history and fully explain the process to try and help you better understand.

In the fire department, we have to schedule our leave in advance, this is to ensure we meet the minimal manning requirements for each shift. We have a "draft" style selection when we chose our leave. Top of the list picks first, then the next on the list, the next, and so on.

We have three tiers. Tier 1: Crew Chiefs, which consist of (in order of highest ranking to lowest) TSgts, GS8's, and SSgt's. Tier 2: Driver/Operators, which consist of GS7's and SrA. Tier 3: Firefighters, which consist of GS6's, A1C's, and Amn.

Tier 1 always selects before Tier 2, and Tier 2 before Tier 3. We are not allowed to have more than one person from the same tier on leave at the same time. So when one person from Tier 1 selects November 29th, 2018 for leave, that day is locked for Tier 1, but one person from Tier 2 and one person from Tier one may still choose that day.

In the past we have sorted the list by rank; TSgt, GS8, SSgt, GS7, SrA, GS6, A1C, Amn. When then sorted that by SCD date, So the TSgts were sorted by their time in service, then the GS8's, SSgts, GS7's, and so on.

Many of our GS8's had an issue with this because they have double the years experience than the TSgt's and believed they earned the right to be at the top of the leave selection list. They discussed this with our boss and our boss agreed. He said we needed to figure out a system we can all agree on and then propose it to him.

We all sat down and agreed that a point system was the best way to go. We then assigned points to the ranks in each Tier and then agreed that one point for every four years of service should be given. I then created an excel spreadsheet for that system and everything worked fine.

Then a GS8 brought up the fact that we have a GS7 that has been in the service for 30 years, if he gets promoted to a GS8 tomorrow, he will jump everybody on that list based on his SCD date even though he had only been a GS8 for a day while the others had been a GS8 for years.

Thats when we decided to go this route.

 

[Gerald Higgins]

OK.

Same question as in post #8

For example, if TSgt BB ranks above GS8 DF, why DON'T the other two TSgts ALSO rank above GS8 DF ?

I can't be certain, but it sounds as if MAYBE everyone has agreed to the points system without actually understanding all the possible implications.
If that's the case, I think you have two choices - stick with the current points system, or ditch it and introduce a better one.