ACOMB

Xlambda · Aug 16, 2021

ACOMB Array Combine . Extracts all combinations of columns values of an array. Is a simpler version of ACOMBINE (that takes its data from different columns of a bigger array, calculates the unique by column of that extraction and only after these steps, calculates all combinations).
MrExcel's latest video published yesterday: Excel All Combinations Using Power Query - 2424 determined me to write this version, (alternative of the cool PQ solution), simple, that does not call any other function, no use of the new functions. Probably I will address a study of all Combinatorics scenarios using lambdas.

Excel Formula:

=LAMBDA(ar,
    LET(a,IF(ar="","",ar),r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(,c),q,SEQUENCE(c)^0,
      IF(OR(r=1,c=1),"check array",LET(x,MOD(ROUNDUP(SEQUENCE(r^c)/r^(c-s),0)-1,r)+1,y,INDEX(a,x,s),
         UNIQUE(FILTER(y,NOT(MMULT(--(y=""),q))))))
    )
)

LAMBDA 1.1.1.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

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

1

if blanks

if 1 clm has dups

combining is by column, for dif.outcome we have to use transpose

2

smpl 1

smpl 2

smpl 3

smpl 4

=ACOMB(TRANSPOSE(Y3:AC4))

3

A

1

X

A

1

X

A

1

X

A

1

X

A

1

X

A

1

X

A

B

C

D

E

A

B

C

D

E

A

1

4

B

2

Y

A

1

Y

B

2

Y

A

1

Y

B

2

Y

A

1

Y

1

2

3

4

5

A

B

C

D

5

A

2

5

C

3

Z

A

1

Z

C

Z

A

1

Z

A

Z

A

1

Z

A

B

C

4

E

A

3

6

A

2

X

D

A

2

X

D

A

2

X

all comb 2^5

A

B

C

4

5

A

4

7

all comb 3^3

A

2

Y

A

2

Y

A

2

Y

32

A

B

3

D

E

A

5

8

27

A

2

Z

A

2

Z

A

2

Z

A

B

3

D

5

B

1

9

A

3

X

B

1

X

B

1

X

check

A

B

3

4

E

B

2

10

check

A

3

Y

B

1

Y

B

1

Y

=ROWS(AE3#)

A

B

3

4

5

B

3

11

=ROWS(E3#)

A

3

Z

B

1

Z

B

1

Z

32

A

2

C

D

E

B

4

12

27

B

1

X

B

2

X

B

2

X

A

2

C

D

5

B

5

13

B

1

Y

B

2

Y

B

2

Y

A

2

C

4

E

C

1

14

B

1

Z

B

2

Z

B

2

Z

A

2

C

4

5

C

2

15

B

2

X

C

1

X

D

1

X

A

2

3

D

E

C

3

16

B

2

Y

C

1

Y

D

1

Y

A

2

3

D

5

C

4

17

B

2

Z

C

1

Z

D

1

Z

A

2

3

4

E

C

5

18

B

3

X

C

2

X

D

2

X

A

2

3

4

5

D

1

19

B

3

Y

C

2

Y

D

2

Y

1

B

C

D

E

D

2

20

B

3

Z

C

2

Z

D

2

Z

1

B

C

D

5

D

3

21

C

1

X

D

1

X

1

B

C

4

E

D

4

22

C

1

Y

D

1

Y

1

B

C

4

5

D

5

23

C

1

Z

D

1

Z

1

B

3

D

E

1

24

C

2

X

D

2

X

1

B

3

D

5

E

2

25

C

2

Y

D

2

Y

1

B

3

4

E

3

26

C

2

Z

D

2

Z

1

B

3

4

5

E

4

27

C

3

X

1

2

C

D

E

5

28

C

3

Y

1

2

C

D

5

29

C

3

Z

1

2

C

4

E

30

1

2

C

4

5

31

1

2

3

D

E

32

1

2

3

D

5

33

1

2

3

4

E

34

1

2

3

4

5

35

ACOMB post

Cell Formulas
Range	Formula
E2,A11,Y10,AK2,AE2,U2,M2	E2	=FORMULATEXT(E3)
E3:G29	E3	=ACOMB(A3:C5)
M3:O26,U3:W20	M3	=ACOMB(I3:K6)
AE3:AI34	AE3	=ACOMB(Y3:AC4)
AK3:AL27	AK3	=ACOMB(TRANSPOSE(Y3:AC4))
Y7	Y7	=2^5
A8	A8	=3^3
Y11	Y11	=ROWS(AE3#)
A12	A12	=ROWS(E3#)
Dynamic array formulas.

Xlambda · Nov 6, 2022

Special post for @gifariz.
Unfolding functionality of ACOMB in 2 parts:
1st part: Main formula, to calculate the index pattern.
2nd part: Cosmetics to filter duplicades records or records with blanks
1st part: Main formula, to calculate the index pattern for combining "c" clm vectors that have "r" elements each.
x,MOD(ROUNDUP(SEQUENCE(r^c)/r^(c-s),0)-1,r)+1 where s,SEQUENCE(,c) in simple steps

ACOMB.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

AD

1

Main formula: Index pattern for combining 3 clm vectors (c=3), 3 elements each (r=3)

2

INDEX pattern

3

A

x

1

step 1.

step 4.

step5.

step 6.

4

B

y

2

sequence(r^c)

step1./step.3

roundup(step 4.,0)

mod(step 5.-1,3)+1

using index pattern

5

C

z

3

=INDEX(B3:D5,V6#,SEQUENCE(,3))

6

1

0.1111

0.3333

1

A

x

1

7

rows=r=3

2

step 2.

0.2222

0.6667

2

1

2

1

2

A

x

2

8

clms=c=3

3

count clm from right to left -1

0.3333

1

3

1

3

1

3

A

x

3

9

4

c-sequence(,c)

0.4444

1.3333

4

1

2

4

1

2

1

A

y

1

10

tot comb =r^c

5

=3-SEQUENCE(,3)

0.5556

1.6667

5

1

2

5

1

2

A

y

2

11

=3^3

6

2

1

0

0.6667

2

6

1

2

6

1

2

3

A

y

3

12

27

7

0.7778

2.3333

7

1

3

7

1

3

1

A

z

1

13

8

0.8889

2.6667

8

1

3

8

1

3

2

A

z

2

14

9

step 3. r^step 2.

1

3

9

1

3

9

1

3

A

z

3

15

10

=3^H11#

1.1111

3.3333

10

2

4

10

2

1

B

x

1

16

11

9

3

1

1.2222

3.6667

11

2

4

11

2

1

2

B

x

2

17

12

1.3333

4

12

2

4

12

2

1

3

B

x

3

18

13

1.4444

4.3333

13

2

5

13

2

1

B

y

1

19

14

1.5556

4.6667

14

2

5

14

2

B

y

2

20

15

1.6667

5

15

2

5

15

2

3

B

y

3

21

16

1.7778

5.3333

16

2

6

16

2

3

1

B

z

1

22

17

1.8889

5.6667

17

2

6

17

2

3

2

B

z

2

23

18

2

6

18

2

6

18

2

3

B

z

3

24

19

2.1111

6.3333

19

3

7

19

3

1

C

x

1

25

20

2.2222

6.6667

20

3

7

20

3

1

2

C

x

2

26

21

2.3333

7

21

3

7

21

3

1

3

C

x

3

27

22

2.4444

7.3333

22

3

8

22

3

2

1

C

y

1

28

23

2.5556

7.6667

23

3

8

23

3

2

C

y

2

29

24

2.6667

8

24

3

8

24

3

2

3

C

y

3

30

25

2.7778

8.3333

25

3

9

25

3

1

C

z

1

31

26

2.8889

8.6667

26

3

9

26

3

2

C

z

2

32

27

3

9

27

3

9

27

3

C

z

3

33

ACOMB 1

Cell Formulas
Range	Formula
F5,H15,B11,H10,R5,V5,Z5,N5	F5	=FORMULATEXT(F6)
F6:F32	F6	=SEQUENCE(3^3)
N6:P32	N6	=F6#/H16#
R6:T32	R6	=ROUNDUP(N6#,0)
V6:X32	V6	=MOD(R6#-1,3)+1
Z6:AB32	Z6	=INDEX(B3:D5,V6#,SEQUENCE(,3))
H11:J11	H11	=3-SEQUENCE(,3)
B12	B12	=3^3
H16:J16	H16	=3^H11#
Dynamic array formulas.

Xlambda · Nov 6, 2022

Same formula, same concept, applied this time to list permutations with repetitions.

ACOMB.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

Main formula: Index pattern for listing all permutations with repetitions for 3 chosen objects out of total 3 objects

2

INDEX pattern

3

step 1.

step 4.

step5.

step 6.

4

A

B

C

sequence(r^c)

step1./step.3

roundup(step 4.,0)

mod(step 5.-1,3)+1

using index pattern

5

6

0.3333

1

A

7

tot obj.=r=3

2

step 2.

0.2222

0.6667

2

1

2

1

2

A

B

8

nr.chosen=c=3

3

count clm from right to left -1

0.3333

1

3

1

3

1

3

A

C

9

4

c-sequence(,c)

0.4444

1.3333

4

1

2

4

1

2

1

A

B

A

10

tot perm =r^c

5

=3-SEQUENCE(,3)

0.5556

1.6667

5

1

2

5

1

2

A

B

11

=3^3

6

2

1

0

0.6667

2

6

1

2

6

1

2

3

A

B

C

12

27

7

0.7778

2.3333

7

1

3

7

1

3

1

A

C

A

13

8

0.8889

2.6667

8

1

3

8

1

3

2

A

C

B

14

=PERMUTATIONA(3,3)

9

step 3. r^step 2.

1

3

9

1

3

9

1

3

A

C

15

27

10

=3^H11#

1.1111

3.3333

10

2

4

10

2

1

B

A

16

11

9

3

1

1.2222

3.6667

11

2

4

11

2

1

2

B

A

B

17

12

1.3333

4

12

2

4

12

2

1

3

B

A

C

18

13

1.4444

4.3333

13

2

5

13

2

1

B

A

19

14

1.5556

4.6667

14

2

5

14

2

B

20

15

1.6667

5

15

2

5

15

2

3

B

C

21

16

1.7778

5.3333

16

2

6

16

2

3

1

B

C

A

22

17

1.8889

5.6667

17

2

6

17

2

3

2

B

C

B

23

18

2

6

18

2

6

18

2

3

B

C

24

19

2.1111

6.3333

19

3

7

19

3

1

C

A

25

20

2.2222

6.6667

20

3

7

20

3

1

2

C

A

B

26

21

2.3333

7

21

3

7

21

3

1

3

C

A

C

27

22

2.4444

7.3333

22

3

8

22

3

2

1

C

B

A

28

23

2.5556

7.6667

23

3

8

23

3

2

C

B

29

24

2.6667

8

24

3

8

24

3

2

3

C

B

C

30

25

2.7778

8.3333

25

3

9

25

3

1

C

A

31

26

2.8889

8.6667

26

3

9

26

3

2

C

B

32

27

3

9

27

3

9

27

3

C

33

ACOMB 1

Cell Formulas
Range	Formula
F5,H15,B14,B11,H10,R5,V5,Z5,N5	F5	=FORMULATEXT(F6)
F6:F32	F6	=SEQUENCE(3^3)
N6:P32	N6	=F6#/H16#
R6:T32	R6	=ROUNDUP(N6#,0)
V6:X32	V6	=MOD(R6#-1,3)+1
Z6:AB32	Z6	=INDEX(B4:D4,V6#)
H11:J11	H11	=3-SEQUENCE(,3)
B12	B12	=3^3
B15	B15	=PERMUTATIONA(3,3)
H16:J16	H16	=3^H11#
Dynamic array formulas.

gifariz · Nov 9, 2022

Amazing! Really elegant and beautiful formula.
Thank you also for the clear step-by-step explanation. Now I understand the intuition of math of main formula.
The explanation format is also very good, I will include this format when explaining my excel to my colleagues.

Xlambda · Nov 13, 2022

gifariz said:
Amazing! Really elegant and beautiful formula.
Thank you also for the clear step-by-step explanation. Now I understand the intuition of math of main formula.
The explanation format is also very good, I will include this format when explaining my excel to my colleagues.

Thanks a lot !!

brunomerola · Nov 21, 2022

@Xlambda, this is amazing.

How would you rewrite it for combinations of k chosen elements in a total of n elements?

This would be the expansion of the traditional formula COMBIN (n,k).

Xlambda · Nov 22, 2022

brunomerola said:
@Xlambda, this is amazing.

How would you rewrite it for combinations of k chosen elements in a total of n elements?

This would be the expansion of the traditional formula COMBIN (n,k).

Thanks!!

I already did an extended combinatorics study here: ARRANGEMENTS
I will also publish an alternative method for combinations w/o repetitions using only random distributions, no patterns algorithms. Need time for editing.

brunomerola · Nov 24, 2022

Xlambda said:
Thanks!!
I already did an extended combinatorics study here: ARRANGEMENTS
I will also publish an alternative method for combinations w/o repetitions using only random distributions, no patterns algorithms. Need time for editing.

Sorry, I hadn't read yet. Just did it and the evolution of your combinatorics study is really impressive.
I believe next step would be overcoming rows limitations in Excel, right? Apparently, total numbers n and numbers chosen nc need, until now, to be small numbers.

Xlambda · Nov 24, 2022

brunomerola said:
Sorry, I hadn't read yet. Just did it and the evolution of your combinatorics study is really impressive.
I believe next step would be overcoming rows limitations in Excel, right? Apparently, total numbers n and numbers chosen nc need, until now, to be small numbers.

Thanks a lot! All combinatorics calculations are performed refining/filtering the permutations with repetitions patterns that needs a lot of rows, and Excel has the hard limit of 1048576 rows. The algorithms work fine and are fast and efficient; not having too much "printing area" is not their fault.
It would have been nice to allow us to exceed the limits, in memory (like in data models, or 5M rows at least).
As for the alternative solution (only for combinations without repetitions) using random distributions, I will post links here when will be ready.

ACOMB

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

gifariz

Board Regular

Xlambda

Well-known Member

brunomerola

New Member

Xlambda

Well-known Member

brunomerola

New Member

Xlambda

Well-known Member

Forum statistics

Share this page

ACOMB

Well-known Member

Well-known Member

Well-known Member

Board Regular

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock