Forum Discussion
Solved
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 table so that the team with the highest score/result is listed in ascending order and keep the team members together?
Any help or hint is very much appreciated.
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 ) )
9 Replies
sql:
select * from OriginalData;
select f01,f02,f03 from (select rowid old_rowid,*,max(f03) over(partition by f02) ord from OriginalData) order by ord,old_rowid;
f01 f02 f03 bbb 1 aaa 1 dddd 1 cccc 1 49 eee 2 kkk 2 hhhh 2 48 zzzz 3 yyyy 3 www 3 xxxx 3 55 f01 f02 f03 eee 2 kkk 2 hhhh 2 48 bbb 1 aaa 1 dddd 1 cccc 1 49 zzzz 3 yyyy 3 www 3 xxxx 3 55 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 ) )- Thanks a lot. I’ll check this out and get back to you on how it goes👍🏻
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
- Thanks a lot !
