Forum Discussion

jerbear's avatar
jerbear
Copper Contributor
Jul 30, 2020

Help sorting in Excel

I need help getting the after sort results in my photo.    
Need Help
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 04, 2020

jerbear 

A formula solution, provided you have the correct version of Excel!

 

= LET(
  unpivot, LET(
     k, SEQUENCE(40,1,0),
     rowNum, MOD(k,10),
     colNum, QUOTIENT(k,10),
     INDEX(Table1,1+rowNum,1+colNum) ),
  sort, LET(
     sorted, SORT(unpivot),
     IF(sorted<>0, sorted, "") ),
  repivot, LET(
     idxArray, SEQUENCE(10,1,0)+10*SEQUENCE(1,4,0),
     INDEX(sort, 1+idxArray) ),
  repivot )

 

 

SergeiBaklan 

I refactored the formula because it was you that first demonstrated the nested LET to me!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 04, 2020

    PeterBartholomew1 

    The idea was in the air, if I found that an hour earlier than you that means nothing.

    Even if LET() is on Current (Preview) now I still avoid to use it in MTC discussion, will wait availability at least on Current.

Resources