Forum Discussion

Akivabuckman's avatar
Akivabuckman
Copper Contributor
Sep 05, 2022

Vertical Slicers

2 dimensional slicers sort left to right, then next line, then left to right, etc.

Anyone know how to make it sort as shown below: vertically down, then a column over, then vertically down again, etc

excel

2 Replies

Sort By
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Sep 07, 2022

    Akivabuckman 

    Now is the time! TOCOL() and WRAPCOLS() have just been unlocked for me in Excel for the Web.

    =WRAPCOLS(SORT(TOCOL(A1:C4,1)),ROWS(A1:C4),"")

     This is how simple the formula can look with the new possibilities.

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Sep 05, 2022

    Akivabuckman 

    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),"")))))

     

    Good luck.

     

    PS: With VSTACK() and CHOOSECOLS() it is certainly even easier. However, these are currently only available to insiders.

Resources