Google-sheets – Filter View: Sort by custom string order

google sheetssorting

Say I have a spreadsheet containing names, dates and a rank.

The spreadsheet is by default sorted by date but I want to create a filter view that sorts the spreadsheet by the rank column, i.e., "General" at the top and "Private" at the bottom.

How would I do this with a custom filter view formula?

Best Answer

This is such a common need that the generalized answer needs to be stated, despite the age of this particular question. Whenever sorting by something Sheets cannot natively understand, one should provide a mapping between the values and some equivalent ordinal numbering.

For neatness perhaps do this on another Sheet "tab" in the same file. Create your mapping from the real-world values and their numerical equivalent—in this case, a list of ranks. A tiny subset, for example:
Column A containing four US Army ranks and Column B with those rank's numbers, all on a new Sheet named Ranks

Then back in your main list's sheet, you can just add a column where each person's rank will be automatically looked up and shown via a formula. Here, I did so in the new Tier column:

Sheet1, containing four populated columns: Rank, First, Last, and Tier. The tier column is populated with rank numbers matching each row's rank from column A as looked up from the Ranks sheet.

Then a formula such as the following can check each row's rank, match it up with the equivalent order number from the other sheet, and show that number here. This column may then be used for sorting, and either hidden or deleted.

=VLOOKUP(A2,Ranks!A$2:B$5,2,FALSE)

Several formulas could be used, but in that example, in D2 I used VLOOKUP to take column A's text "Captian", find it in the Ranks sheet, and show Captian's corresponding order number, 21. After dragging that formula down the column, each Rank will be converted into it's matching order number, making the entire sheet ready for easy sorting.

Related Topic