Sorting by Military Rank

Arnie volunteers for a veteran's group. He put the group's members in a workbook based on where they provided their service, such as Army, Navy, Air Force, etc., one worksheet per service branch. One column on each worksheet is the member's current rank or rank when they retired. Arnie wondered if there is a way to sort the membership on a single worksheet based on rank.

There are several ways that Arnie could approach this task. Before looking at each of them, though, it should be noted that Arnie doesn't indicate the country in which he is located. So, in providing approaches to the issue, I need to make some assumptions, the biggest one relating to country. So, I'm going to use United States armed forces rankings in this tip. Even so, the techniques described could be easily adapted to working with armed forces rankings in other countries.

The second assumption is based on what Arnie stated—that he has individual worksheets for each service branch. There are rank differences among service branches, and rather than go into all those differences here, I'm going to focus on a single service branch (US Army) to illustrate the approaches. The approach can then be adjusted, as necessary, for each branch.

With all that in mind, let's turn to three different approaches that Arnie could use for sorting his group by military rank.

Use a Helper Column

One approach is to use a helper column to indicate a sorting number for the individual's rank. For instance, you might use 1 to indicate a general, 2 for a colonel, 3 for major, 4 for captain, and so on. Then, when it comes to sort, you could use the helper column as the primary sorting key.

You could take it one step further and add your ranks in a named range. For instance, you could place the ranks in column Z, select the range, and assign it the name ArmyRanks. Then, in your helper column you could use this formula that references the individual's rank in column C:

=MATCH(C1,ArmyRanks,0)

The returned value would be the numeric position of the rank within the ArmyRanks range. You can then sort by the values in the helper column.

Use the Person's Paygrade

In the US armed services, rank and paygrade are closely related. You can easily see this if you visit the following webpage:

https://www.defense.gov/resources/insignia/

Note the paygrade column, E-1 through E-9, W-1 through W-5, and O-1 through O-10. If you put a person's paygrade in a column, it makes sorting much easier. The drawback, of course, is that some paygrades have more than one rank assigned to them—for instance, in the Army both Corporal and Specialist are E-4 and both Master Sergeant and First Sergeant are E-8. In those instances, you could use the paygrade as the primary sort key and the rank as the secondary sort key.

One big advantage to the paygrade approach is that it applies to all branches of service, whereas rank does not. For instance, a captain in the Navy is very different from a captain in the Army. A Navy captain is equivalent to an Army colonel; both are at paygrade 0-6. Sort by the paygrade, and you have the ranks in the proper order for each service branch.

Create a Custom Sort Order

Excel allows you to create custom sort orders for situations just like those being faced by Arnie. The first step is to list, in proper ascending order, the ranks. Then, follow these steps:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or a later version display the File tab of the ribbon and then click Options.)
2. Select Advanced at the left side of the dialog box or, in Excel 2007, make sure Popular is selected.
3. Click Edit Custom Lists. (You may need to scroll down a bit to see the button.) Excel displays the Custom Lists dialog box.
4. Click in the box to the left of the Import button.
5. Within the worksheet, select the list of ranks you created.
6. Click the Import button. Your list of ranks now appears at the list of custom lists at the left side of the dialog box.
7. Click on OK.

Now you can use this custom sorting order with the ranks. Since rank ordering differs by service branch, you will need to create a custom sort order for each branch.

You can find more about custom sort orders at this Microsoft site:

https://support.microsoft.com/en-us/office/cba3d67a-c5cb-406f-9b14-a02205834d72

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7924) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.