Forum Discussion

Bazz02's avatar
Bazz02
Copper Contributor
Sep 17, 2021

excel in windows 10.

how to create a button that sorts by one column and then assigns an order in another column from one to 20.

 

  • Hi, What do you mean by 'order from 1 to 20'? What is 1, what is 30? Kind regards Hans
  • Bazz02 

    Following up some of the ideas already presented,

    is returned by the formula

    = LET(
        ranking, RANK(Table1[Value],Table1[Value],-1),
        output,  IF({0,1}, Table1[Text], ranking),
        SORT(output)
      )

    The ranking is placed first because, for some mysterious reason, RANK requires range references and not arrays.  The next row is a device to combine the newly calculated rankings with the text column before it is sorted by rank.  

    • bosinander's avatar
      bosinander
      Steel Contributor

      I see that absolute references static arrays comes more and more handy. 

      Using CHOOSE allows to combine multiple dynamic arrays.

      =SORT(CHOOSE({3\1\2}; Table1[Text]; Table1[Value]; Table1[Rank]))

      Also noted that RANK is around for backwards compatibility.

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        I like Tables because of the way in which they adjust to the actual data (as well as providing structured references) so that Excel is not left to infer what is intended by the user; it is explicit.
  • hansleroy's avatar
    hansleroy
    Iron Contributor
    Hi, What do you mean by 'order from 1 to 20'? What is 1, what is 30? Kind regards Hans

Resources