Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 837
- Office Version
- 365
- Platform
- Windows
AUNQSRT sorts ascending, descending, or extracts unique values by rows of an array, left aligned.
Excel Formula:
=LAMBDA(ar,k,
LET(xk,OR(k={-1,0,1}),
r,ROWS(ar),
c,COLUMNS(ar),
sr,SEQUENCE(r),
s,SEQUENCE(r*c),
q,QUOTIENT(s-1,c)+1,
m,MOD(s-1,c)+1,
a,INDEX(IF(ar="","",ar),q,m),
x,a<>"",qf,FILTER(CHOOSE({1,2},q,a),x),
y,SWITCH(k,-1,SORT(qf,{1,2},{1,-1}),0,UNIQUE(qf),1,SORT(qf,{1,2})),
na,INDEX(y,,2),
nq,INDEX(y,,1),
fq,FREQUENCY(nq,sr),
p,INDEX(fq,sr),
nc,MAX(p),
nsa,IF(p>=SEQUENCE(,nc),SEQUENCE(r,nc)),
nsr,SMALL(nsa,SEQUENCE(SUM(p))),
rs,IFNA(XLOOKUP(nsa,nsr,na),""),
IF(xk,rs,"check var -1(desc),0(unique),1(asc)")
)
)| LAMBDA 5.0.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | a | -1 | 1 | 0 | 1 | b | a | |||
| 2 | 2 | -3 | c | c | 2 | 0 | ||||
| 3 | % | d | c | d | c | 3 | ||||
| 4 | ||||||||||
| 5 | a | -1 | 1 | 0 | b | |||||
| 6 | 2 | -3 | c | 0 | ||||||
| 7 | % | d | c | 3 | ||||||
| 8 | ||||||||||
| 9 | b | a | a | 1 | 1 | 0 | -1 | |||
| 10 | c | c | 2 | 2 | 0 | -3 | ||||
| 11 | d | d | c | c | % | 3 | ||||
| 12 | ||||||||||
| 13 | -1 | 0 | 1 | 1 | a | a | b | |||
| 14 | -3 | 0 | 2 | 2 | c | c | ||||
| 15 | 3 | % | c | c | d | d | ||||
| 16 | ||||||||||
AUNQSRT post | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A5:E7 | A5 | =AUNQSRT(A1:G3,) |
| A9:G11 | A9 | =AUNQSRT(A1:G3,-1) |
| A13:G15 | A13 | =AUNQSRT(A1:G3,1) |
| Dynamic array formulas. | ||
Last edited by a moderator:
Upvote
0
