Sep 05 2022 09:27 AM
Sep 05 2022 10:55 AM - edited Sep 05 2022 11:13 AM
I have created a lambda function for you that will satisfy your sorting request. But it uses some relatively new functions like BYROW() and MAKEARRAY(). I hope it works for you. In Excel for the Web it works in any case.
=LAMBDA(in, LET( r,ROWS(in), c,COLUMNS(in), ro,MOD(SEQUENCE(r*c,1,0),r)+1, co,INT(SEQUENCE(r*c,1,0)/r)+1, oc,BYROW(INDEX(in,ro,co),LAMBDA(ir,IF(ir="",UNICHAR(5000),ir))), so,SORT(oc), MAKEARRAY(r,c,LAMBDA(rl,cl,SUBSTITUTE(INDEX(so,rl+((cl-1)*r)),UNICHAR(5000),"")))))
PS: With VSTACK() and CHOOSECOLS() it is certainly even easier. However, these are currently only available to insiders.