MSORT is a powerful and versatile array sorting solution that allows for sorting all or a subset of the columns (or rows) together or independently as well as having various options for dealing with the unsortables (blanks, logicals, and errors)
MSORT takes five parameters, one required and four optional, as follows:
A) The first parameter, required, specifies the function input and can be any cell/array/range
Note that the input array can contain any type of data, i.e. numbers, texts, blanks, logicals, and errors (for a full list of all data types, refer to the latest update of XTYPE); however, only numbers and texts are really sortable; thus, MSORT provides various options for treating the unsortables (blanks, logicals, and errors) (see sections B and C below)
B) The second parameter, optional, specifies the unsortable data type(s) that are intended to be ignored while sorting and takes one of eight arguments:
0 or omitted, for not ignoring any unsortable data types
1, for ignoring blanks
2, for ignoring errors
3, for ignoring logicals
4, for ignoring blanks and errors
5, for ignoring blanks and logicals
6, for ignoring errors and logicals
7, for ignoring blanks, errors, and logicals
Note that this parameter handles the placement location of the unsortables within the output array (which is the same exact size as the input array); any cell(s) containing ignored data type(s) will be either a) kept in their original places relative to the input array or b) piled at the spots indicated by the "ignore_mode" parameter, while the other unsortables will change places when sort order is changed between ascending and descending (see section C below)
Note that only one argument can be entered in this parameter; entering more than one number via array brackets "{}" would lead to a #VALUE! error
Note that besides being omitted, only the specified eight arguments can be used in this array; entering anything else will lead to a #VALUE! error
C) The third parameter, optional, specifies the placement location, within the output array, of the unsortables that are not ignored and takes one of three arguments:
0 or omitted, for keeping unsortables in their original places relative to the input array
1, for piling the unsortables at the bottom or right of the output array depending on whether the input array is sorted by columns or rows, respectively
2, for piling the unsortables at the top or left of the output array depending on whether the input array is sorted by columns or rows, respectively
Note that only one argument can be entered in this parameter; entering more than one number via array brackets "{}" would lead to a #VALUE! error
Note that besides being omitted, only the specified three arguments can be used in this array; entering anything else will lead to a #VALUE! error
D) The fourth parameter, optional, specifies the sort order(s) for the columns or rows and takes one of four arguments:
omitted, for the default ascending sort order
-1, for descending sort order(s)
1, for ascending sort order(s)
"", for skipping sort order(s) within an array of sort orders
Note that whether the columns or rows are sorted at the specified order is specified via the "sort_by_row" parameter (see section E below)
Note that if this parameter is left omitted, the default ascending sort order will be applied to all columns or rows
Note that if only one number (i.e. -1 or 1) is entered, the ascending or descending sort order will be applied to all columns or rows; for this to happen, the single number must not be in array brackets "{}"; if the single number is within array brackets, the specified sort order will be applied only to the first column or row (see notes below)
Note that each of the columns or rows can be sorted independently of the others by specifying multiple sort orders
Note that if more than one sort order need to be entered, they must be included in array brackets "{}" per native syntax
Note that if fewer sort orders than the total count of columns or rows of the input array are entered, the remaining columns or rows that do not correspond to sort orders will be left unsorted in the output array
Note that if more sort orders than the total count of columns or rows of the input array are entered, the sort order(s) past the last point of correspondence to the columns or rows will be ignored by the function; thus, if the input array is later expanded, those additional sort orders will be applied to the input array
Note that one or more columns or rows at any position within the input array can be left unsorted by skipping the sort order(s) corresponding to those columns or rows, and this is achieved by entering blanks, i.e. "", within the sort order array (e.g. {"",1,-1,"",-1,"","",1})
Note that if the blank symbol "" is used outside of the array brackets, i.e. as a single sort order argument, the function will return a #VALUE! error; thus, its use is meant only for when multiple sort orders need to be entered via array brackets "{}"
Note that besides being omitted, only the specified three arguments can be used in this array; entering anything else will lead to a #VALUE! error
E) The fifth parameter, optional, specifies whether the rows or columns of the input array are sorted and takes one of two arguments:
0 or omitted or FALSE, for sorting by columns
1 or TRUE or any number other than 0, for sorting by rows
Note that besides being omitted, only one real number or TRUE/FALSE can be entered in this parameter; entering more than one number or logical will cause the function to crash to a #VALUE! error
Note that if anything other than the specified allowed arguments is entered, the function will return a #VALUE! error
This function was essentially coded for by @djclements as discussed in the following thread especially starting at post #5; I only made minor changes and added some code for argument checking of the parameters
www.mrexcel.com
Special thanks to @djclements for this fabulous code.
(You can download my template from the link in my signature below, which makes it easier to obtain/test the functions compared to manually pasting from the text/XL2BB)
Note that the final IF function in the formula is intended only for argument checking of the parameters and is not strictly required for the core function to work
MSORT
ISNUMBERS
IO (shortened form of ISOMITTED)
MSORT takes five parameters, one required and four optional, as follows:
A) The first parameter, required, specifies the function input and can be any cell/array/range
Note that the input array can contain any type of data, i.e. numbers, texts, blanks, logicals, and errors (for a full list of all data types, refer to the latest update of XTYPE); however, only numbers and texts are really sortable; thus, MSORT provides various options for treating the unsortables (blanks, logicals, and errors) (see sections B and C below)
B) The second parameter, optional, specifies the unsortable data type(s) that are intended to be ignored while sorting and takes one of eight arguments:
0 or omitted, for not ignoring any unsortable data types
1, for ignoring blanks
2, for ignoring errors
3, for ignoring logicals
4, for ignoring blanks and errors
5, for ignoring blanks and logicals
6, for ignoring errors and logicals
7, for ignoring blanks, errors, and logicals
Note that this parameter handles the placement location of the unsortables within the output array (which is the same exact size as the input array); any cell(s) containing ignored data type(s) will be either a) kept in their original places relative to the input array or b) piled at the spots indicated by the "ignore_mode" parameter, while the other unsortables will change places when sort order is changed between ascending and descending (see section C below)
Note that only one argument can be entered in this parameter; entering more than one number via array brackets "{}" would lead to a #VALUE! error
Note that besides being omitted, only the specified eight arguments can be used in this array; entering anything else will lead to a #VALUE! error
C) The third parameter, optional, specifies the placement location, within the output array, of the unsortables that are not ignored and takes one of three arguments:
0 or omitted, for keeping unsortables in their original places relative to the input array
1, for piling the unsortables at the bottom or right of the output array depending on whether the input array is sorted by columns or rows, respectively
2, for piling the unsortables at the top or left of the output array depending on whether the input array is sorted by columns or rows, respectively
Note that only one argument can be entered in this parameter; entering more than one number via array brackets "{}" would lead to a #VALUE! error
Note that besides being omitted, only the specified three arguments can be used in this array; entering anything else will lead to a #VALUE! error
D) The fourth parameter, optional, specifies the sort order(s) for the columns or rows and takes one of four arguments:
omitted, for the default ascending sort order
-1, for descending sort order(s)
1, for ascending sort order(s)
"", for skipping sort order(s) within an array of sort orders
Note that whether the columns or rows are sorted at the specified order is specified via the "sort_by_row" parameter (see section E below)
Note that if this parameter is left omitted, the default ascending sort order will be applied to all columns or rows
Note that if only one number (i.e. -1 or 1) is entered, the ascending or descending sort order will be applied to all columns or rows; for this to happen, the single number must not be in array brackets "{}"; if the single number is within array brackets, the specified sort order will be applied only to the first column or row (see notes below)
Note that each of the columns or rows can be sorted independently of the others by specifying multiple sort orders
Note that if more than one sort order need to be entered, they must be included in array brackets "{}" per native syntax
Note that if fewer sort orders than the total count of columns or rows of the input array are entered, the remaining columns or rows that do not correspond to sort orders will be left unsorted in the output array
Note that if more sort orders than the total count of columns or rows of the input array are entered, the sort order(s) past the last point of correspondence to the columns or rows will be ignored by the function; thus, if the input array is later expanded, those additional sort orders will be applied to the input array
Note that one or more columns or rows at any position within the input array can be left unsorted by skipping the sort order(s) corresponding to those columns or rows, and this is achieved by entering blanks, i.e. "", within the sort order array (e.g. {"",1,-1,"",-1,"","",1})
Note that if the blank symbol "" is used outside of the array brackets, i.e. as a single sort order argument, the function will return a #VALUE! error; thus, its use is meant only for when multiple sort orders need to be entered via array brackets "{}"
Note that besides being omitted, only the specified three arguments can be used in this array; entering anything else will lead to a #VALUE! error
E) The fifth parameter, optional, specifies whether the rows or columns of the input array are sorted and takes one of two arguments:
0 or omitted or FALSE, for sorting by columns
1 or TRUE or any number other than 0, for sorting by rows
Note that besides being omitted, only one real number or TRUE/FALSE can be entered in this parameter; entering more than one number or logical will cause the function to crash to a #VALUE! error
Note that if anything other than the specified allowed arguments is entered, the function will return a #VALUE! error
This function was essentially coded for by @djclements as discussed in the following thread especially starting at post #5; I only made minor changes and added some code for argument checking of the parameters
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
Hello, This question is related to my previous question below 😀: https://www.mrexcel.com/board/threads/how-to-generate-the-specified-sequence-of-numbers-based-on-incrementing-portions-of-the-sequence.1264083/#post-6213799 I took Stephen's amazing solution and spiced it up to make my LAMBDA...
Special thanks to @djclements for this fabulous code.
(You can download my template from the link in my signature below, which makes it easier to obtain/test the functions compared to manually pasting from the text/XL2BB)
Note that the final IF function in the formula is intended only for argument checking of the parameters and is not strictly required for the core function to work
MSORT
Excel Formula:
=LAMBDA(array,[ignore],[ignore_mode],[sort_orders],[sort_by_row],
LET(
a,IF(TRIM(array)="","",array),
m,ignore_mode,
o,sort_orders,
c,NOT(IF(IO(sort_by_row),0,INDEX(sort_by_row,1))),
cλ,LAMBDA(t,f,IF(c,t,f)),
h,ROWS(a),
w,COLUMNS(a),
i,SEQUENCE(h),
j,SEQUENCE(,w),
x,CHOOSE(ignore+1,
0,
IFERROR(a="",0),
ISERROR(a),
ISLOGICAL(a),
IFERROR(a,"")="",
IFERROR(a="",0)+ISLOGICAL(a),
ISERROR(a)+ISLOGICAL(a),
(IFERROR(a,"")="")+ISLOGICAL(a)
),
z,TOCOL(IF({1},cλ(EXPAND(TAKE(TOROW(o),,w),,w,0),EXPAND(TAKE(TOCOL(o),h),h,,0)),a),,c),
s,IF(
OR(x),
LET(
y,NOT(x),
vλ,LAMBDA(arr,incl,FILTER(TOCOL(arr,,c),TOCOL(incl,,c))),
nλ,LAMBDA(arr,incl,TOCOL(IFS(incl,arr),2,c)),
sλ,LAMBDA(one,two,IF(m=2,VSTACK(two,one),VSTACK(one,two))),
_v1,vλ(a,y),
_v2,vλ(a,x),
_n1,nλ(cλ(j,i),y),
_b1,sλ(_n1,nλ(cλ(j,i),x)),
_b2,IF(m,EXPAND(1,h*w,,1),VSTACK(nλ(cλ(i,j),y),nλ(cλ(i,j),x))),
IF(
TYPE(o)=64,
SWITCH(z,
1,SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,1),_v2),_b1,1,_b2,1),
-1,SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,-1),_v2),_b1,1,_b2,1),
TOCOL(a,,c)),
SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,o),_v2),_b1,1,_b2,1)
)
),
LET(
v,TOCOL(a,,c),
n,TOCOL(IF({1},cλ(j,i),a),,c),
IF(
TYPE(o)=64,
SWITCH(z,
1,SORTBY(v,n,1,v,1),
-1,SORTBY(v,n,1,v,-1),
v
),
SORTBY(v,n,1,v,o)
)
)
),
IF(
AND(
ISNUMBERS(ignore,1,1),
ISNUMBERS(ignore_mode,1,1,{0,1,2,3,4,5,6,7}),
ISNUMBERS(sort_orders,1,,{1,-1,""},1),
ISNUMBERS(sort_by_row,1,1,{TRUE,FALSE},2)
),
cλ(WRAPCOLS(s,h),WRAPROWS(s,w)),
#VALUE!
)
)
)ISNUMBERS
Excel Formula:
=LAMBDA(reference,[if_omitted],[single],[match_set],[extend_match],
IF(
IO(reference),
IF(IO(if_omitted),FALSE,if_omitted),
LET(
k,TOCOL(IF(reference="","",reference)),
l,TOCOL(IF(match_set="","",match_set)),
m,IF(IO(extend_match),0,extend_match),
Mλ,LAMBDA(w,IF(PRODUCT(ISNUMBER(w)*1),TRUE,FALSE)),
Aλ,LAMBDA(u,IFERROR(ROWS(TOCOL(u)),1)),
Bλ,LAMBDA(x,LET(v,Aλ(x),IF(v=1,IF(IFERROR(IF(IO(x),0,x),#VALUE!),TRUE,FALSE),#VALUE!))),
Cλ,LAMBDA(r,s,IF(PRODUCT(NOT(ISERROR(MATCH(r,s,0)))*1),TRUE,FALSE)),
Nλ,LAMBDA(o,LET(
p,ROWS(o)*COLUMNS(o),
IF(
IFERROR(p,1)>1,
FALSE,
IF((IF(ISERROR(o),IF(ERROR.TYPE(o)=14,1,0),0))+(IF(ISERROR(p),1,0))=2,TRUE,FALSE)
)
)
),
f,Bλ(single),
i,IF(
IO(match_set),
Mλ(k),
SWITCH(m,
0,LET(j,FILTER(l,ISNUMBER(l)),IF(Nλ(j),FALSE,Cλ(k,j))),
1,Cλ(k,l),
OR(
LET(g,FILTER(k,ISNUMBER(k)),IF(Nλ(k),1,Mλ(k))),
LET(q,FILTER(k,NOT(ISNUMBER(k))),IF(Nλ(q),1,Cλ(q,l)))
)
)
),
b,IF(f,IF(Aλ(k)=1,i,FALSE),i),
IF(AND(ISLOGICAL(f),Cλ(m,{0,1,2}),ISLOGICAL(Bλ(m))),b,#VALUE!)
)
)
)IO (shortened form of ISOMITTED)
Excel Formula:
=LAMBDA(parameter,
ISOMITTED(parameter)
)| MSORT.xlsx | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | |||||||||||||||||||||
| 2 | #CALC! | ||||||||||||||||||||
| 3 | |||||||||||||||||||||
| 4 | input array: | data types: | |||||||||||||||||||
| 5 | 10 | 8 | -1 | -3 | -2 | -1 | 4 | -5 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||
| 6 | -8 | 4 | 9 | 4 | TRUE | 10 | 1 | 1 | 32 | 1 | 1 | 41 | 1 | 31 | |||||||
| 7 | book | #N/A | 9 | 10 | #VALUE! | 8 | #REF! | 8 | 2 | 167 | 1 | 1 | 163 | 1 | 164 | 1 | |||||
| 8 | -1 | -7 | 2 | FALSE | 0 | 2 | #NUM! | 1 | 1 | 1 | 42 | 1 | 31 | 1 | 166 | ||||||
| 9 | 4 | #DIV/0! | -9 | 0 | 4 | 10 | FALSE | -9 | 1 | 162 | 1 | 1 | 1 | 1 | 42 | 1 | |||||
| 10 | note | -2 | -4 | 2 | 2 | TRUE | 2 | 1 | 34 | 1 | 33 | 1 | 1 | 41 | |||||||
| 11 | -8 | 6 | -1 | 6 | 7 | 2 | 9 | 1 | 1 | 1 | 1 | 1 | 33 | 1 | 1 | ||||||
| 12 | |||||||||||||||||||||
| 13 | |||||||||||||||||||||
| 14 | -8 | -7 | -9 | -4 | -2 | -1 | 2 | -9 | -5 | -3 | -2 | -1 | -1 | 4 | 8 | 10 | |||||
| 15 | -8 | -2 | -1 | -3 | 0 | 2 | 2 | -5 | -8 | 4 | 4 | 9 | 10 | TRUE | |||||||
| 16 | -1 | 4 | -1 | 0 | 4 | 8 | 2 | 8 | 8 | 8 | 9 | 10 | book | #N/A | #VALUE! | #REF! | |||||
| 17 | 4 | 6 | 2 | 6 | 4 | 10 | 4 | 9 | -7 | -1 | 0 | 2 | 2 | FALSE | #NUM! | ||||||
| 18 | 10 | 8 | 9 | 9 | 7 | 10 | -9 | -9 | 0 | 4 | 4 | 10 | FALSE | #DIV/0! | |||||||
| 19 | book | #N/A | 10 | FALSE | TRUE | -4 | -2 | 2 | 2 | note | TRUE | ||||||||||
| 20 | note | #DIV/0! | FALSE | #VALUE! | TRUE | #REF! | #NUM! | -8 | -1 | 2 | 6 | 6 | 7 | 9 | |||||||
| 21 | |||||||||||||||||||||
| 22 | note | #N/A | FALSE | #VALUE! | TRUE | #REF! | #NUM! | 10 | 8 | 4 | -1 | -1 | -2 | -3 | -5 | ||||||
| 23 | book | #DIV/0! | 10 | FALSE | TRUE | TRUE | 10 | 9 | 4 | 4 | -8 | ||||||||||
| 24 | 10 | 8 | 9 | 9 | 7 | 10 | #N/A | #VALUE! | #REF! | book | 10 | 9 | 8 | 8 | |||||||
| 25 | 4 | 6 | 2 | 6 | 4 | 10 | 4 | 9 | #NUM! | FALSE | 2 | 2 | 0 | -1 | -7 | ||||||
| 26 | -1 | 4 | -1 | 0 | 4 | 8 | 2 | 8 | #DIV/0! | FALSE | 10 | 4 | 4 | 0 | -9 | -9 | |||||
| 27 | -8 | -2 | -1 | -3 | 0 | 2 | 2 | -5 | TRUE | note | 2 | 2 | -2 | -4 | |||||||
| 28 | -8 | -7 | -9 | -4 | -2 | -1 | 2 | -9 | 9 | 7 | 6 | 6 | 2 | -1 | -8 | ||||||
| 29 | |||||||||||||||||||||
| 30 | note | #N/A | 9 | FALSE | #VALUE! | TRUE | #REF! | #NUM! | 10 | 8 | 4 | -1 | -1 | -2 | -3 | -5 | |||||
| 31 | book | #DIV/0! | 2 | 10 | 7 | 10 | FALSE | TRUE | TRUE | 10 | 9 | 4 | 4 | -8 | |||||||
| 32 | 10 | 8 | -1 | 9 | 4 | 8 | 10 | 9 | #N/A | #VALUE! | #REF! | book | 10 | 9 | 8 | 8 | |||||
| 33 | 4 | 6 | -1 | 6 | 4 | 2 | 4 | 8 | #NUM! | FALSE | 2 | 2 | 0 | -1 | -7 | ||||||
| 34 | -1 | 4 | -9 | 0 | 0 | -1 | 2 | -5 | #DIV/0! | FALSE | 10 | 4 | 4 | 0 | -9 | -9 | |||||
| 35 | -8 | -2 | -3 | -2 | 2 | -9 | TRUE | note | 2 | 2 | -2 | -4 | |||||||||
| 36 | -8 | -7 | -4 | 2 | 9 | 7 | 6 | 6 | 2 | -1 | -8 | ||||||||||
| 37 | |||||||||||||||||||||
| 38 | note | 8 | FALSE | TRUE | FALSE | TRUE | 10 | 8 | 4 | -1 | -1 | -2 | -3 | -5 | |||||||
| 39 | book | 6 | 10 | 7 | 10 | TRUE | 10 | 9 | 4 | 4 | -8 | ||||||||||
| 40 | 10 | 4 | 9 | 9 | 4 | 4 | 9 | book | 10 | 9 | 8 | 8 | #N/A | #VALUE! | #REF! | ||||||
| 41 | 4 | -2 | 2 | 6 | 4 | 10 | 2 | 8 | FALSE | 2 | 2 | 0 | -1 | -7 | #NUM! | ||||||
| 42 | -1 | -7 | -1 | 0 | 0 | 8 | 2 | -5 | FALSE | 10 | 4 | 4 | 0 | -9 | -9 | #DIV/0! | |||||
| 43 | -8 | #N/A | -1 | -3 | -2 | 2 | 2 | -9 | TRUE | note | 2 | 2 | -2 | -4 | |||||||
| 44 | -8 | #DIV/0! | -9 | -4 | #VALUE! | -1 | #REF! | #NUM! | 9 | 7 | 6 | 6 | 2 | -1 | -8 | ||||||
| 45 | |||||||||||||||||||||
| 46 | note | #N/A | 10 | #VALUE! | #REF! | #NUM! | 10 | 8 | 4 | -1 | -1 | -2 | -3 | -5 | |||||||
| 47 | book | #DIV/0! | 9 | 10 | 10 | 9 | 4 | 4 | -8 | TRUE | |||||||||||
| 48 | 10 | 8 | 9 | 6 | 7 | 10 | 4 | 9 | #N/A | #VALUE! | #REF! | book | 10 | 9 | 8 | 8 | |||||
| 49 | 4 | 6 | 2 | 0 | 4 | 8 | 2 | 8 | #NUM! | 2 | 2 | 0 | -1 | -7 | FALSE | ||||||
| 50 | -1 | 4 | -1 | -3 | 4 | 2 | 2 | -5 | #DIV/0! | 10 | 4 | 4 | 0 | -9 | -9 | FALSE | |||||
| 51 | -8 | -2 | -1 | -4 | 0 | -1 | 2 | -9 | note | 2 | 2 | -2 | -4 | TRUE | |||||||
| 52 | -8 | -7 | -9 | FALSE | -2 | TRUE | FALSE | TRUE | 9 | 7 | 6 | 6 | 2 | -1 | -8 | ||||||
| 53 | |||||||||||||||||||||
| 54 | note | 8 | 9 | FALSE | 7 | TRUE | FALSE | TRUE | 10 | 8 | 4 | -1 | -1 | -2 | -3 | -5 | |||||
| 55 | book | 6 | 2 | 10 | 4 | 10 | 10 | 9 | TRUE | 10 | 9 | 4 | 4 | -8 | |||||||
| 56 | 10 | 4 | -1 | 9 | 4 | 8 | 4 | 8 | book | 10 | 9 | 8 | 8 | #N/A | #VALUE! | #REF! | |||||
| 57 | 4 | -2 | -1 | 6 | 0 | 2 | 2 | -5 | FALSE | 2 | 2 | 0 | -1 | -7 | #NUM! | ||||||
| 58 | -1 | -7 | -9 | 0 | -2 | -1 | 2 | -9 | FALSE | 10 | 4 | 4 | 0 | -9 | -9 | #DIV/0! | |||||
| 59 | -8 | #N/A | -3 | #VALUE! | 2 | TRUE | note | 2 | 2 | -2 | -4 | ||||||||||
| 60 | -8 | #DIV/0! | -4 | #REF! | #NUM! | 9 | 7 | 6 | 6 | 2 | -1 | -8 | |||||||||
| 61 | |||||||||||||||||||||
| 62 | note | #N/A | 9 | 10 | #VALUE! | 10 | #REF! | #NUM! | 10 | 8 | 4 | -1 | -1 | -2 | -3 | -5 | |||||
| 63 | book | #DIV/0! | 2 | 9 | 7 | 8 | 10 | 9 | 10 | 9 | 4 | 4 | -8 | TRUE | |||||||
| 64 | 10 | 8 | -1 | 6 | 4 | 2 | 4 | 8 | #N/A | #VALUE! | #REF! | book | 10 | 9 | 8 | 8 | |||||
| 65 | 4 | 6 | -1 | 0 | 4 | -1 | 2 | -5 | #NUM! | 2 | 2 | 0 | -1 | -7 | FALSE | ||||||
| 66 | -1 | 4 | -9 | -3 | 0 | TRUE | 2 | -9 | #DIV/0! | 10 | 4 | 4 | 0 | -9 | -9 | FALSE | |||||
| 67 | -8 | -2 | -4 | -2 | 2 | note | 2 | 2 | -2 | -4 | TRUE | ||||||||||
| 68 | -8 | -7 | FALSE | FALSE | TRUE | 9 | 7 | 6 | 6 | 2 | -1 | -8 | |||||||||
| 69 | |||||||||||||||||||||
| 70 | note | 8 | 10 | 10 | 10 | 8 | 4 | -1 | -1 | -2 | -3 | -5 | |||||||||
| 71 | book | 6 | 9 | 7 | 4 | 9 | 10 | 9 | 4 | 4 | -8 | TRUE | |||||||||
| 72 | 10 | 4 | 9 | 6 | 4 | 10 | 2 | 8 | book | 10 | 9 | 8 | 8 | #N/A | #VALUE! | #REF! | |||||
| 73 | 4 | -2 | 2 | 0 | 4 | 8 | 2 | -5 | 2 | 2 | 0 | -1 | -7 | FALSE | #NUM! | ||||||
| 74 | -1 | -7 | -1 | -3 | 0 | 2 | 2 | -9 | 10 | 4 | 4 | 0 | -9 | -9 | #DIV/0! | FALSE | |||||
| 75 | -8 | #N/A | -1 | -4 | -2 | -1 | #REF! | #NUM! | note | 2 | 2 | -2 | -4 | TRUE | |||||||
| 76 | -8 | #DIV/0! | -9 | FALSE | #VALUE! | TRUE | FALSE | TRUE | 9 | 7 | 6 | 6 | 2 | -1 | -8 | ||||||
| 77 | |||||||||||||||||||||
| 78 | note | 8 | 9 | 10 | 7 | 10 | 10 | 9 | 10 | 8 | 4 | -1 | -1 | -2 | -3 | -5 | |||||
| 79 | book | 6 | 2 | 9 | 4 | 8 | 4 | 8 | 10 | 9 | 4 | 4 | -8 | TRUE | |||||||
| 80 | 10 | 4 | -1 | 6 | 4 | 2 | 2 | -5 | book | 10 | 9 | 8 | 8 | #N/A | #VALUE! | #REF! | |||||
| 81 | 4 | -2 | -1 | 0 | 0 | -1 | 2 | -9 | 2 | 2 | 0 | -1 | -7 | FALSE | #NUM! | ||||||
| 82 | -1 | -7 | -9 | -3 | -2 | TRUE | 2 | 10 | 4 | 4 | 0 | -9 | -9 | #DIV/0! | FALSE | ||||||
| 83 | -8 | #N/A | -4 | #VALUE! | #REF! | #NUM! | note | 2 | 2 | -2 | -4 | TRUE | |||||||||
| 84 | -8 | #DIV/0! | FALSE | FALSE | TRUE | 9 | 7 | 6 | 6 | 2 | -1 | -8 | |||||||||
| 85 | |||||||||||||||||||||
| 86 | note | 8 | 9 | 10 | 7 | 10 | 10 | 9 | 10 | 8 | 4 | -1 | -1 | -2 | -3 | -5 | |||||
| 87 | book | 6 | 9 | 4 | TRUE | 4 | 10 | 9 | 4 | 4 | TRUE | -8 | |||||||||
| 88 | 10 | #N/A | 2 | 6 | #VALUE! | 8 | #REF! | 8 | book | #N/A | 10 | 9 | #VALUE! | 8 | #REF! | 8 | |||||
| 89 | 4 | 4 | -1 | FALSE | 4 | 2 | #NUM! | 2 | 2 | 0 | FALSE | -1 | -7 | #NUM! | |||||||
| 90 | -1 | #DIV/0! | -1 | 0 | 0 | 2 | FALSE | -5 | 10 | #DIV/0! | 4 | 4 | 0 | -9 | FALSE | -9 | |||||
| 91 | -8 | -2 | -3 | -1 | 2 | TRUE | note | 2 | 2 | -2 | -4 | TRUE | |||||||||
| 92 | -8 | -7 | -9 | -4 | -2 | 2 | -9 | 9 | 7 | 6 | 6 | 2 | -1 | -8 | |||||||
| 93 | |||||||||||||||||||||
| 94 | -8 | -7 | 9 | -4 | 7 | -1 | 2 | -9 | -5 | -3 | -2 | -1 | -1 | 4 | 8 | 10 | |||||
| 95 | -8 | -2 | 2 | -3 | 4 | 2 | 2 | -5 | -8 | 4 | 4 | 9 | 10 | TRUE | |||||||
| 96 | -1 | 4 | -1 | 0 | 4 | 8 | 2 | 8 | book | 10 | 9 | 8 | 8 | #N/A | #VALUE! | #REF! | |||||
| 97 | 4 | 6 | -1 | 6 | 0 | 10 | 4 | 9 | -7 | -1 | 0 | 2 | 2 | FALSE | #NUM! | ||||||
| 98 | 10 | 8 | -9 | 9 | -2 | TRUE | 10 | 10 | 4 | 4 | 0 | -9 | -9 | #DIV/0! | FALSE | ||||||
| 99 | book | #N/A | 10 | #VALUE! | #REF! | #NUM! | -4 | -2 | 2 | 2 | note | TRUE | |||||||||
| 100 | note | #DIV/0! | FALSE | FALSE | TRUE | -8 | -1 | 2 | 6 | 6 | 7 | 9 | |||||||||
| 101 | |||||||||||||||||||||
MSORT | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =LAMBDA(array,[ignore],[ignore_mode],[sort_orders],[sort_by_row],LET(a,IF(TRIM(array)="","",array),m,ignore_mode,o,sort_orders,c,NOT(IF(IO(sort_by_row),0,INDEX(sort_by_row,1))),cλ,LAMBDA(t,f,IF(c,t,f)),h,ROWS(a),w,COLUMNS(a),i,SEQUENCE(h),j,SEQUENCE(,w),x,CHOOSE(ignore+1,0,IFERROR(a="",0),ISERROR(a),ISLOGICAL(a),IFERROR(a,"")="",IFERROR(a="",0)+ISLOGICAL(a),ISERROR(a)+ISLOGICAL(a),(IFERROR(a,"")="")+ISLOGICAL(a)),z,TOCOL(IF({1},cλ(EXPAND(TAKE(TOROW(o),,w),,w,0),EXPAND(TAKE(TOCOL(o),h),h,,0)),a),,c),s,IF(OR(x),LET(y,NOT(x),vλ,LAMBDA(arr,incl,FILTER(TOCOL(arr,,c),TOCOL(incl,,c))),nλ,LAMBDA(arr,incl,TOCOL(IFS(incl,arr),2,c)),sλ,LAMBDA(one,two,IF(m=2,VSTACK(two,one),VSTACK(one,two))),_v1,vλ(a,y),_v2,vλ(a,x),_n1,nλ(cλ(j,i),y),_b1,sλ(_n1,nλ(cλ(j,i),x)),_b2,IF(m,EXPAND(1,h*w,,1),VSTACK(nλ(cλ(i,j),y),nλ(cλ(i,j),x))),IF(TYPE(o)=64,SWITCH(z,1,SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,1),_v2),_b1,1,_b2,1),-1,SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,-1),_v2),_b1,1,_b2,1),TOCOL(a,,c)),SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,o),_v2),_b1,1,_b2,1))),LET(v,TOCOL(a,,c),n,TOCOL(IF({1},cλ(j,i),a),,c),IF(TYPE(o)=64,SWITCH(z,1,SORTBY(v,n,1,v,1),-1,SORTBY(v,n,1,v,-1),v),SORTBY(v,n,1,v,o)))),IF(AND(ISNUMBERS(ignore,1,1),ISNUMBERS(ignore_mode,1,1,{0,1,2,3,4,5,6,7}),ISNUMBERS(sort_orders,1,,{1,-1,""},1),ISNUMBERS(sort_by_row,1,1,{TRUE,FALSE},2)),cλ(WRAPCOLS(s,h),WRAPROWS(s,w)),#VALUE!))) |
| K5:R11 | K5 | =XTYPE(B5:I11,3) |
| D6 | D6 | =IF(1,"") |
| C9 | C9 | =1/0 |
| B14:I20 | B14 | =MSORT(B5:I11) |
| K14:R20 | K14 | =MSORT(B5:I11,,,,1) |
| B22:I28 | B22 | =MSORT(B5:I11,,,-1) |
| K22:R28 | K22 | =MSORT(B5:I11,,,-1,1) |
| B30:I36 | B30 | =MSORT(B5:I11,1,1,-1) |
| K30:R36 | K30 | =MSORT(B5:I11,1,,-1,1) |
| B38:I44 | B38 | =MSORT(B5:I11,2,1,-1) |
| K38:R44 | K38 | =MSORT(B5:I11,2,1,-1,1) |
| B46:I52 | B46 | =MSORT(B5:I11,3,1,-1) |
| K46:R52 | K46 | =MSORT(B5:I11,3,1,-1,1) |
| B54:I60 | B54 | =MSORT(B5:I11,4,1,-1) |
| K54:R60 | K54 | =MSORT(B5:I11,4,1,-1,1) |
| B62:I68 | B62 | =MSORT(B5:I11,5,1,-1) |
| K62:R68 | K62 | =MSORT(B5:I11,5,1,-1,1) |
| B70:I76 | B70 | =MSORT(B5:I11,6,1,-1) |
| K70:R76 | K70 | =MSORT(B5:I11,6,1,-1,1) |
| B78:I84 | B78 | =MSORT(B5:I11,7,1,-1) |
| K78:R84 | K78 | =MSORT(B5:I11,7,1,-1,1) |
| B86:I92 | B86 | =MSORT(B5:I11,7,,-1) |
| K86:R92 | K86 | =MSORT(B5:I11,7,,-1,1) |
| B94:I100 | B94 | =MSORT(B5:I11,7,1,{1,1,-1,1,-1,1,1,1}) |
| K94:R100 | K94 | =MSORT(B5:I11,7,1,{1,1,-1,1,-1,1,1,1},1) |
| Dynamic array formulas. | ||
Upvote
0
