How to sort a spreadsheet by "dan and kyu" rank?

Question

In the game of go as well as in martial arts, shogi, for tea ceremonies and for flower arrangements, players have a rank expressed in dan and kyu.

Dan and kyu form a simple numerical system:

30 kyu < 29 kyu < 28 kyu < ... < 3 kyu < 2 kyu < 1 kyu < 1 dan < 2 dan < ... < 8 dan < 9 dan

In other words:

• Kyu can be considered negative numbers/
• Dan can be considered positive numbers.
• There is no "zero", that is, rank 1 dan comes directly above rank 1 kyu.

Kyu are usually abbreviated k and dan d.

As a result, when organising tournaments we often have spreadsheets with a "rank" column, which contains abbreviated ranks such as 13k or 2d, and we want to be able to sort the spreadsheet by rank, with stronger players at the top and weaker players at the bottom.

What's the simplest way to sort the spreadsheet by the rank column, which is kinda numerical but also contains letters "d" and "k"?

I am okay with creating a new column with a formula that automatically computes a numerical value from the rank value, but I want to avoid forcing the user to manually type more information than the abbreviated rank. In particular I want to avoid having to type "03k" instead of just "3k" for a 3 kyu player, and I want to avoid having to manually fill two columns with one column being numeric and the other containing the "dan or kyu" unit.

Answer 1

This formula will convert a rank value from 30k,...,2k,1k,1d,2d,...,9d to a negative or positive number -30,...,-2,-1,0,1,...,8, respectively:

=LEFT(D4;LEN(D4)-1) * IF(RIGHT(D4,1)="k";-1;1) + IF(RIGHT(D4,1)="d";-1;0)

The LEFT(...) part extracts the number, the first IF(...) multiplies kyu ranks by -1 and the second IF(...) offsets dan ranks by 1 so that the difference between 1k and 1d is exactly 1.

Answer 2

For fun, using the newly available 365 regex function, you can try the following:

B2: =SORTBY(A2:A6,--REGEXREPLACE(A2:A6,"(?<dan>(\d+)d)|(?<kyu>(\d+)+k)","${dan:+$2:-$4}"),-1)

(Enter only in B2; the formula results will spill down to row 6)

If you need to test that the Input is correctly filled out (eg: starts with an integer and ends with a d or k, that logic can be added)

Answer 3

try:

=INDEX(LET(a, TOCOL(A:A, 1), x, 
 SPLIT(REGEXREPLACE(a, "(\d+)(d|k)", "$1×$2"), "×"), 
 SORT(a, INDEX(x,,2), 1, INDEX(x,,1), 1)))