Forum Discussion
HELP with my FILTER + SORTBY FUNCTIONS
Hello Everyone !
Here I have a LIST which I want to FILTER and SORTBY last name then first name.
Ci-joint: https://www.cjoint.com/c/MJorHJzrNNa
Thanks !
I have had a go at filling in "z YELLOWS"
Might not make it any the more intelligible though!
PeterBartholomew1 , Boulakgnobot
Just cosmetic, added drop-down filter and keep all in each formula.
I wonder how wide the audience is for such refinement?
I liked the idea of the gender dropdown and the way in with "ALL" was implemented had a touch of class. I did, however, find that
VSTACK( {"First Name","Last Name","Gender"}, HSTACK( CHOOSECOLS(f,1), CHOOSECOLS(f,2), CHOOSECOLS(f,3) ) )overdid things, though there might be benefit in having
VSTACK( {"Last Name","First Name","Gender"}, HSTACK( CHOOSECOLS(f,{2,1,3}) ) )say. I liked the way you turned the weekday to text rather than simply using number-formatting to create the illusion of text.
= TEXT(1+seq,"ddd")is worth remembering for future use! I had wondered whether the final formula could be cast as a single Lambda function that calls the sorting and matching of days from the prior exercises as functions. The result would appear rather as a rabbit emerging from the magician's top hat.
Maybe we could be accused of excessive self-indulgence at that point!
EXERCISE 02:
create temp table aa as select ((row_number() over ()-1)%6)+1 w,((row_number() over ()-1)/6) grp,row_number() over () N,* from group_fill_week where f03 like 'F'; create temp table bb as select *,case w when 1 then 'Monday' when 2 then 'Tuesday' when 3 then 'Wednesday' when 4 then 'Thursday' when 5 then 'Friday' when 6 then 'Saturday' end days from aa; select days,N,f01||' '||f02 full_name from bb; create temp table cc as select grp,days,f01||' '||f02 full_name from bb; cli_create_two_dim_no_order~cc~days~full_name; select colIndex[1:] from cc_two_dim;I think I have two distinct exercises muddled in together but, for what it's worth, my approach is to develop a solution step by step, aiming for a readable syntax.
= LET( Joinλ, LAMBDA(s, TEXTJOIN(" ",,s)), females, FILTER(names, gender="F"), sorted, SORT(females, {2,1}), fullNms, BYROW(sorted, Joinλ), WRAPROWS(fullNms, 6, "") )1. Define a Lambda function for use later
2. Filter the array to return females only
3. Sort the names by surname first, then first name
4. Combine each name to a single string
5. Reshape list, filling with blanks if required
Thanks you for your answer and help !
Unfortunately this is too complicated for me to understand !
Here is one: in E26
=LET(Filtered, FILTER($A$6:$C$20, ($C$6:$C$20="F")+($C$6:$C$20="M")), SORTBY(Filtered, INDEX(Filtered, 0, 2), 1, INDEX(Filtered, 0, 1), 1))
I'll leave the rest for others.
