SORTROWS Sorts a 2D array by row. Optionally, zeros can be classed as "high" (as in sorted last). The array returns in the same dimensions as the original.
This function utilises other Lambda functions that can be viewed here:
ARRAY2DTO1D
ROWNUMBERS
ARRAY1DTO2D
This function utilises other Lambda functions that can be viewed here:
ARRAY2DTO1D
ROWNUMBERS
ARRAY1DTO2D
Excel Formula:
=LAMBDA(array,zerosToEnd,
LET(a,array,
mx,MAX(a)+1,
a1D,ARRAY2DTO1D(a),
a1DRw,ARRAY2DTO1D(ROWNUMBERS(a)),
zte,IF(zerosToEnd=1,IF(a1D=0,mx,a1D),a1D),
srt,SORTBY(a1D,a1DRw,1,zte,1),
return,ARRAY1DTO2D(srt,ROWS(a)),
return)
)| Lambda - Last Cell and Split.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Original Array | ||||||||||||
| 2 | 1 | 0 | 3 | 4 | 5 | 6 | 0 | 8 | 0 | 10 | |||
| 3 | 1 | 2 | 3 | 0 | 5 | 0 | 7 | 0 | 0 | 10 | |||
| 4 | 1 | 2 | 0 | 4 | 5 | 6 | 7 | 8 | 0 | 0 | |||
| 5 | |||||||||||||
| 6 | Sorted Array, zeros at end | ||||||||||||
| 7 | 1 | 3 | 4 | 5 | 6 | 8 | 10 | 0 | 0 | 0 | |||
| 8 | 1 | 2 | 3 | 5 | 7 | 10 | 0 | 0 | 0 | 0 | |||
| 9 | 1 | 2 | 4 | 5 | 6 | 7 | 8 | 0 | 0 | 0 | |||
| 10 | |||||||||||||
| 11 | Sorted Array, zeros in place | ||||||||||||
| 12 | 0 | 0 | 0 | 1 | 3 | 4 | 5 | 6 | 8 | 10 | |||
| 13 | 0 | 0 | 0 | 0 | 1 | 2 | 3 | 5 | 7 | 10 | |||
| 14 | 0 | 0 | 0 | 1 | 2 | 4 | 5 | 6 | 7 | 8 | |||
| 15 | |||||||||||||
Sheet1 | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:K4 | B2 | ={1,0,3,4,5,6,0,8,0,10;1,2,3,0,5,0,7,0,0,10;1,2,0,4,5,6,7,8,0,0} |
| B7:K9 | B7 | =SORTROWS(B2#,1) |
| B12:K14 | B12 | =SORTROWS(B2#,) |
| Dynamic array formulas. | ||
Last edited by a moderator:
Upvote
0
