Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 860
- Office Version
- 365
- Platform
- Windows
ACOMBINE list all possible combinations between all unique elements found on each column of an array. calls AUNQSRT , AUNIQUE , ACLEAN , AXLOOKUP
VBA Code:
=LAMBDA(a,cl,
LET(y,INDEX(a,SEQUENCE(ROWS(a)),IF(AND(cl),cl,SEQUENCE(,COLUMNS(a)))),
u,AUNIQUE(y,-1),v,TRANSPOSE(AUNQSRT(TRANSPOSE(u),1)),
r,ROWS(v),c,COLUMNS(v),s,SEQUENCE(,c),
x,MOD(ROUNDUP(SEQUENCE(r^c)/r^ABS(s-c),0)-1,r)*c+s,
ACLEAN(AXLOOKUP(x,IF(v="",0,SEQUENCE(r,c)),v,,,),1)
)
)
LAMBDA 7.0.xlsx | |||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | |||
1 | list all possible combinations between all unique elements found on each column of an array | ||||||||||||||||||||||||||||||
2 | ( in combinations order is not important, only in permutations) | ex.4 change placement order | ex.5 | =ACOMBINE(V3:X6,) | |||||||||||||||||||||||||||
3 | ex.1 | =ACOMBINE(A4:B5,) | ex.3 | =ACOMBINE(J4:L18,) | =ACOMBINE(J4:L18,{1,3,2}) | Mac | white | East | Mac | blue | East | ||||||||||||||||||||
4 | A | 1 | A | 1 | B | c | x | A | a | x | A | x | a | Win | blue | West | Mac | blue | Midwest | ||||||||||||
5 | B | 2 | A | 2 | A | a | x | A | a | y | A | x | b | Sam | red | Midwest | Mac | blue | West | ||||||||||||
6 | B | 1 | B | b | x | A | b | x | A | x | c | green | Mac | green | East | ||||||||||||||||
7 | B | 2 | A | c | y | A | b | y | A | y | a | Mac | green | Midwest | |||||||||||||||||
8 | B | b | y | A | c | x | A | y | b | Mac | green | West | |||||||||||||||||||
9 | ex.2 | =ACOMBINE(A10:C12,) | B | b | x | A | c | y | A | y | c | Mac | red | East | |||||||||||||||||
10 | A | a | x | A | a | x | A | a | y | B | a | x | B | x | a | Mac | red | Midwest | |||||||||||||
11 | B | b | y | A | a | y | A | c | y | B | a | y | B | x | b | Mac | red | West | |||||||||||||
12 | c | A | b | x | B | b | y | B | b | x | B | x | c | Mac | white | East | |||||||||||||||
13 | A | b | y | A | b | x | B | b | y | B | y | a | Mac | white | Midwest | ||||||||||||||||
14 | A | c | x | B | a | x | B | c | x | B | y | b | Mac | white | West | ||||||||||||||||
15 | A | c | y | A | c | y | B | c | y | B | y | c | Sam | blue | East | ||||||||||||||||
16 | B | a | x | B | b | x | Sam | blue | Midwest | ||||||||||||||||||||||
17 | B | a | y | A | a | y | Sam | blue | West | ||||||||||||||||||||||
18 | B | b | x | A | a | y | Sam | green | East | ||||||||||||||||||||||
19 | B | b | y | Sam | green | Midwest | |||||||||||||||||||||||||
20 | B | c | x | Formula extracts unique values by columns | Sam | green | West | ||||||||||||||||||||||||
21 | B | c | y | and sorts them ascending top to bottom | Sam | red | East | ||||||||||||||||||||||||
22 | =AUNIQUE(J4:L18,-1) | =TRANSPOSE(AUNQSRT(TRANSPOSE(J23#),1)) | Sam | red | Midwest | ||||||||||||||||||||||||||
23 | ex.6 patterns | =ACOMBINE(A24:C25,) | B | c | x | A | a | x | Sam | red | West | ||||||||||||||||||||
24 | 1 | 2 | 3 | 1 | 2 | 1 | A | a | y | B | b | y | other functions on minisheet | Sam | white | East | |||||||||||||||
25 | 3 | 2 | 1 | 1 | 2 | 3 | b | c | AUNIQUE | Sam | white | Midwest | |||||||||||||||||||
26 | 3 | 2 | 1 | step1 | step2 | AUNQSRT | Sam | white | West | ||||||||||||||||||||||
27 | 3 | 2 | 3 | Win | blue | East | |||||||||||||||||||||||||
28 | If same element can be found on more clms | Win | blue | Midwest | |||||||||||||||||||||||||||
29 | will be repeated accordingly | Win | blue | West | |||||||||||||||||||||||||||
30 | =ACOMBINE(J31:K32,) | Win | green | East | |||||||||||||||||||||||||||
31 | x | x | x | 1 | Win | green | Midwest | ||||||||||||||||||||||||
32 | y | 1 | x | x | Win | green | West | ||||||||||||||||||||||||
33 | y | 1 | Win | red | East | ||||||||||||||||||||||||||
34 | y | x | Win | red | Midwest | ||||||||||||||||||||||||||
35 | Win | red | West | ||||||||||||||||||||||||||||
36 | Win | white | East | ||||||||||||||||||||||||||||
37 | Win | white | Midwest | ||||||||||||||||||||||||||||
38 | Win | white | West | ||||||||||||||||||||||||||||
39 | |||||||||||||||||||||||||||||||
ACOMBINE post |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Z2,E23,N22,E9,N3,R3,E3 | Z2 | =FORMULATEXT(Z3) |
Z3:AB38 | Z3 | =ACOMBINE(V3:X6,) |
E4:F7,N31:O34 | E4 | =ACOMBINE(A4:B5,) |
N4:P15 | N4 | =ACOMBINE(J4:L18,) |
R4:T15 | R4 | =ACOMBINE(J4:L18,{1,3,2}) |
E10:G21 | E10 | =ACOMBINE(A10:C12,) |
I22,M30 | I22 | =FORMULATEXT(J23) |
J23:L25 | J23 | =AUNIQUE(J4:L18,-1) |
N23:P25 | N23 | =TRANSPOSE(AUNQSRT(TRANSPOSE(J23#),1)) |
E24:G27 | E24 | =ACOMBINE(A24:C25,) |
Dynamic array formulas. |
Upvote
0