Sorting a table with groups of people (no VBA)

Hi, I have a table with groups of people in a team. The individual team can vary from 3 to 4 persons. The table list the group and their group score/result (se attached image). How can I sort the t...

excel

office 365

Lorenzo

Oct 19, 2024

espensun

If you already have GROUPBY:

=LET(
  grouped,      GROUPBY( Table[Group], Table[Res.], SUM,, 0 ),
  filled_res,   XLOOKUP( Table[Group], CHOOSECOLS( grouped, 1 ), CHOOSECOLS( grouped, 2) ),
  sorted,       SORTBY( Table,
                  filled_res, 1,
                  SEQUENCE( ROWS( Table ) ), 1
                ),
  IF( ISBLANK( sorted ), "", sorted )
)

SORTBY_Res_3.xlsx12 KB

Lorenzo

Silver Contributor

Oct 19, 2024

Hi espensun

Did it with a Table (not mandatory) - sample attached

=LET(
  unique_group, UNIQUE( Table[Group] ),
  group_res,    XLOOKUP( unique_group, Table[Group], Table[Res.],,, -1 ),
  filled_res,   XLOOKUP( Table[Group], unique_group, group_res ),
  sorted,       SORTBY( Table,
                  filled_res, 1,
                  SEQUENCE( ROWS( Table ) ), 1
                ),
  IF( ISBLANK( sorted ), "", sorted )
)

EDIT: shortened the formula and applied Cond. Format. to separate the Groups

SORTBY_Res_2.xlsx12 KB