Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 860
- Office Version
- 365
- Platform
- Windows
AKEEP array keep, keeps only certain chars of an array, all the others are removed and replaced with a delimiter or not. calls AREMOVE
Other functions used on minisheet ASPLIT , AUNQSRT , AFILTER , AAGGREGATE
Other functions used on minisheet ASPLIT , AUNQSRT , AFILTER , AAGGREGATE
Excel Formula:
=LAMBDA(a,k,d,
LET(x,CONCAT(AREMOVE(a,k,)),l,LEN(x),
m,SORT(UNIQUE(UNICODE(MID(x,SEQUENCE(l),1)))),
AREMOVE(a,CONCAT(UNICHAR(m)),d)
)
)
LAMBDA 7.0.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | sample | =AKEEP(A2:A3,C3,) | ||||||||||
2 | akjhda786875, %&/jnlsdj(kjbkl)cg vhjk45678 | keep only | 77%g7 | |||||||||
3 | hghg354678· %&/hghj_>Z3s54dr6fty8 6545DRFTGHJ | g7H% | gg7%gH | |||||||||
4 | ||||||||||||
5 | extract unique dates for each row in ascending order | |||||||||||
6 | dfg><rd23/3/21-uy21/3/21g-·$%&23/03/21CVrt%& | |||||||||||
7 | 05/04/21ftgy$%&02/04/21gkh-<·$%&2/4/21 | |||||||||||
8 | ||||||||||||
9 | =AKEEP(A6:A7,T_CHARS(1,,"/"),"-") | =ASPLIT(A10#,"-") | =AUNQSRT(AUNQSRT(C10#,),1) | |||||||||
10 | 23/3/21-21/3/21-23/03/21 | 44278 | 44276 | 44278 | 21-03-21 | 23-03-21 | ||||||
11 | 05/04/21-02/04/21-2/4/21 | 44291 | 44288 | 44288 | 02-04-21 | 05-04-21 | ||||||
12 | ||||||||||||
13 | sum the values found on each row | |||||||||||
14 | 25.465jdlk·$%DF-56.34hg$%TYT-ghdWERT"·$%%&46.5 | |||||||||||
15 | -18.6JHGytr&%$-JHGFuye&%$45.17JHG-JKHuytr(&$ | |||||||||||
16 | ||||||||||||
17 | =AKEEP(A14:A15,T_CHARS(1,,"-."),"\") | =ASPLIT(A18#,"\") | ||||||||||
18 | 25.465\-56.34\-\46.5 | 25.465 | -56.34 | - | 46.5 | |||||||
19 | -18.6\-\45.17\- | -18.6 | - | 45.17 | - | |||||||
20 | =AFILTER(C18#,1,) | =AAGGREGATE(C21#,5,) | ||||||||||
21 | other lambdas used in the minisheet | 25.465 | -56.34 | 46.5 | 15.625 | |||||||
22 | ASPLIT | -18.6 | 45.17 | 26.57 | ||||||||
23 | AUNQSRT | |||||||||||
24 | AFILTER | |||||||||||
25 | AAGGREGATE | |||||||||||
26 | ||||||||||||
AKEEP post |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E1,G20,C20,A17,C17,A9,G9,C9 | E1 | =FORMULATEXT(E2) |
E2:E3 | E2 | =AKEEP(A2:A3,C3,) |
A10:A11 | A10 | =AKEEP(A6:A7,T_CHARS(1,,"/"),"-") |
C10:E11 | C10 | =ASPLIT(A10#,"-") |
G10:H11 | G10 | =AUNQSRT(AUNQSRT(C10#,),1) |
A18:A19 | A18 | =AKEEP(A14:A15,T_CHARS(1,,"-."),"\") |
C18:F19 | C18 | =ASPLIT(A18#,"\") |
C21:E22 | C21 | =AFILTER(C18#,1,) |
G21:G22 | G21 | =AAGGREGATE(C21#,5,) |
Dynamic array formulas. |
Upvote
0