Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 837
- 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
