Search code examples
excelexcel-formula

How to sort text values the same way as a web-based staircase generator

I'm trying to sort some text values in the same way as you can with this web-based staircase generator (https://authoredup.com/tools/text-staircase).

enter image description here

Here are my values in A2:A21:

Bhutan
The United Kingdom of Great Britain and Northern Ireland
South Africa
The Republic of the Marshall Islands
Netherlands
Central African Republic
Dominica
The Democratic Republic of the Congo
Peru
Papua New Guinea
Jamaica
Antigua and Barbuda
El Salvador
Bosnia and Herzegovina
Japan
The Democratic Republic of São Tomé and Príncipe
Nicaragua
The Federated States of Micronesia
Bangladesh
The Independent and Sovereign Republic of Kiribati

There are four variations, and I've managed to do the two easier ones. For short to long, the formula in B2 is =SORTBY(A2:A21,LEN(A2:A21)), and for long to short in C2, it's =SORTBY(A2:A21,LEN(A2:A21),-1).

However, I'm struggling to come up with formulas for the pyramid and chevron styles (I've included the values in the screenshot to show what should be returned).

What can I do?

Solution

  • Sort the list twice. The first time, sort it by length, as you are already doing.

    The second time, sort it by a function of its position in the length-sorted list (you can use the SEQUENCE function here), for example -2^[Position]

    Position -2^[Position] ¦ Sorted Position Sorted -2^[Position]
    1 -2 ¦ 5 -32
    2 4 ¦ 3 -8
    3 -8 ¦ 1 -2
    4 16 ¦ 2 4
    5 -32 ¦ 4 16

    This will move the first item from the first sorted list to the middle of the second sorted list, and alternate the subsequent items before or after it.

    =SORTBY(SORTBY(A2:A21,LEN(A2:A21), 1), -2^SEQUENCE(COUNTA(A2:A21)), 1)

    To swap between "Pyramid" and "Chevron", swap the order of the first sort — this will determine if the middle value is the shortest or the longest.

    To simplify things somewhat, you can also use the LET function to define the range once per function:

    #Short to Long
=LET(_list, A2:A21, SORTBY(_list, LEN(_list), 1))

#Long to Short
=LET(_list, A2:A21, SORTBY(_list, LEN(_list), -1))

#Pyramid
=LET(_list, A2:A21, SORTBY(SORTBY(_list, LEN(_list), -1), -2^SEQUENCE(COUNTA(_list)), 1))

#Chevron
=LET(_list, A2:A21, SORTBY(SORTBY(_list, LEN(_list), 1), -2^SEQUENCE(COUNTA(_list)), 1))