Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 860
- Office Version
- 365
- Platform
- Windows
AUNIQUE extracts unique values, horizontal (for each row) left aligned , vertical (for each column) top aligned , all (in a vertical array)
calls AFLATTEN , AUNQSRT
calls AFLATTEN , AUNQSRT
Excel Formula:
=LAMBDA(a,k,
LET(x,OR(k={-1,0,1}),
af,AFLATTEN(a),
au,UNIQUE(FILTER(af,af<>"")),
IF(x,SWITCH(k,0,au,1,AUNQSRT(a,),-1,TRANSPOSE(AUNQSRT(TRANSPOSE(a),))),"-1 vert., 0 all vert , 1 horiz.")
)
)
LAMBDA 5.0.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | d | d | 4 | unq. H | ↔ | k=1 | ||||
2 | 1 | g | h | d | 4 | |||||
3 | 1 | d | h | 1 | g | h | ||||
4 | a | 3 | 4 | 1 | d | h | ||||
5 | d | 4 | 4 | a | 3 | 4 | ||||
6 | 3 | 3 | d | 4 | ||||||
7 | c | 2 | 2 | 3 | ||||||
8 | c | 2 | ||||||||
9 | ||||||||||
10 | uniq.all | ↕ | k=0 | unq. V | ↕ | k= -1 | ||||
11 | d | d | d | 4 | ||||||
12 | 4 | 1 | g | h | ||||||
13 | 1 | a | 3 | 3 | ||||||
14 | g | 3 | 4 | 2 | ||||||
15 | h | c | 2 | |||||||
16 | a | |||||||||
17 | 3 | |||||||||
18 | c | |||||||||
19 | 2 | |||||||||
20 | ||||||||||
AUNIQUE post |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:G8 | E2 | =AUNIQUE(A1:C7,1) |
A11:A19 | A11 | =AUNIQUE(A1:C7,) |
E11:G15 | E11 | =AUNIQUE(A1:C7,-1) |
Dynamic array formulas. |
Upvote
0