Forum Discussion

espensun's avatar
espensun
Copper Contributor
Oct 17, 2024
Solved

Sorting a two column table using SORTBY and if values are identical, use a table to rank them

Hi,
I have a two-column table with "Name" and "Points". To sort this table, I use the SORTBY function and that works as I would like it to do. However, if two or more persons have the same number of points I would like to sort the person with the highest ranking before next one. The ranking information is in another (two column) table. I'm struggling to find a solution to do this (without VBA). If anyone has an idea how I can solve this or point me in a direction it will be much appreciated.

excel
Formulas and Functions
office 365

6 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 17, 2024

    Hi espensun 

     

    Seems to do it... (used Tables - not mandatory):

    =LET(
  ranks,    XLOOKUP( Points[Name], Ranks[Name], Ranks[Rank] ),
  stacked,  HSTACK( Points, ranks ),
  sorted,   SORTBY( stacked, Points[Points], -1, ranks, -1 ),
  DROP( sorted,, -1)
)
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Nov 06, 2024

      espensun 

      A more direct option (not mine):

      =SORTBY( Points, Points[Score], -1,
  XLOOKUP(Points[Name], Ranks[Name], Ranks[Rank] ), 1
)

       

      • espensun's avatar
        espensun
        Copper Contributor
        Nov 06, 2024

        Lorenzo
        Thank you so much for following up on this😍
        I'll check it out and see what works best for me👌

    • espensun's avatar
      espensun
      Copper Contributor
      Oct 17, 2024

      Thank you so much for your reply Lorenzo. I'll give that a go👍