ACOMB

=ACOMB(a)

a
array

Extracts all combinations of columns values of an array

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
1if blanksif 1 clm has dupscombining is by column, for dif.outcome we have to use transpose
2smpl 1=ACOMB(A3:C5)smpl 2=ACOMB(I3:K6)smpl 3=ACOMB(Q3:S6)smpl 4=ACOMB(Y3:AC4)=ACOMB(TRANSPOSE(Y3:AC4))
3A1XA1XA1XA1XA1XA1XABCDEABCDEA1
4B2YA1YB2YA1YB2YA1Y12345ABCD5A2
5C3ZA1ZCZA1ZAZA1ZABC4EA3
6A2XDA2XDA2Xall comb 2^5ABC45A4
7all comb 3^3A2YA2YA2Y32AB3DEA5
827A2ZA2ZA2ZAB3D5B1
9A3XB1XB1Xcheck AB34EB2
10check A3YB1YB1Y=ROWS(AE3#)AB345B3
11=ROWS(E3#)A3ZB1ZB1Z32A2CDEB4
1227B1XB2XB2XA2CD5B5
13B1YB2YB2YA2C4EC1
14B1ZB2ZB2ZA2C45C2
15B2XC1XD1XA23DEC3
16B2YC1YD1YA23D5C4
17B2ZC1ZD1ZA234EC5
18B3XC2XD2XA2345D1
19B3YC2YD2Y1BCDED2
20B3ZC2ZD2Z1BCD5D3
21C1XD1X1BC4ED4
22C1YD1Y1BC45D5
23C1ZD1Z1B3DEE1
24C2XD2X1B3D5E2
25C2YD2Y1B34EE3
26C2ZD2Z1B345E4
27C3X12CDEE5
28C3Y12CD5
29C3Z12C4E
3012C45
31123DE
32123D5
331234E
3412345
35
ACOMB post
Cell Formulas
RangeFormula
E2,A11,Y10,AK2,AE2,U2,M2E2=FORMULATEXT(E3)
E3:G29E3=ACOMB(A3:C5)
M3:O26,U3:W20M3=ACOMB(I3:K6)
AE3:AI34AE3=ACOMB(Y3:AC4)
AK3:AL27AK3=ACOMB(TRANSPOSE(Y3:AC4))
Y7Y7=2^5
A8A8=3^3
Y11Y11=ROWS(AE3#)
A12A12=ROWS(E3#)
Dynamic array formulas.
 
Upvote 1
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Main formula: Index pattern for combining 3 clm vectors (c=3), 3 elements each (r=3)
2INDEX pattern
3Ax1step 1.step 4. step5.step 6.
4By2sequence(r^c)step1./step.3roundup(step 4.,0)mod(step 5.-1,3)+1using index pattern
5Cz3=SEQUENCE(3^3)=F6#/H16#=ROUNDUP(N6#,0)=MOD(R6#-1,3)+1=INDEX(B3:D5,V6#,SEQUENCE(,3))
610.11110.33331111111Ax1
7rows=r=32step 2. 0.22220.66672112112Ax2
8clms=c=33count clm from right to left -10.333313113113Ax3
94c-sequence(,c)0.44441.33334124121Ay1
10tot comb =r^c5=3-SEQUENCE(,3)0.55561.66675125122Ay2
11=3^362100.666726126123Ay3
122770.77782.33337137131Az1
1380.88892.66678138132Az2
149step 3. r^step 2.139139133Az3
1510=3^H11#1.11113.3333102410211Bx1
16119311.22223.6667112411212Bx2
17121.33334122412213Bx3
18131.44444.3333132513221By1
19141.55564.6667142514222By2
20151.66675152515223By3
21161.77785.3333162616231Bz1
22171.88895.6667172617232Bz2
231826182618233Bz3
24192.11116.3333193719311Cx1
25202.22226.6667203720312Cx2
26212.33337213721313Cx3
27222.44447.3333223822321Cy1
28232.55567.6667233823322Cy2
29242.66678243824323Cy3
30252.77788.3333253925331Cz1
31262.88898.6667263926332Cz2
322739273927333Cz3
33
ACOMB 1
Cell Formulas
RangeFormula
F5,H15,B11,H10,R5,V5,Z5,N5F5=FORMULATEXT(F6)
F6:F32F6=SEQUENCE(3^3)
N6:P32N6=F6#/H16#
R6:T32R6=ROUNDUP(N6#,0)
V6:X32V6=MOD(R6#-1,3)+1
Z6:AB32Z6=INDEX(B3:D5,V6#,SEQUENCE(,3))
H11:J11H11=3-SEQUENCE(,3)
B12B12=3^3
H16:J16H16=3^H11#
Dynamic array formulas.
 
Same formula, same concept, applied this time to list permutations with repetitions.
ACOMB.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Main formula: Index pattern for listing all permutations with repetitions for 3 chosen objects out of total 3 objects
2INDEX pattern
3step 1.step 4. step5.step 6.
4ABCsequence(r^c)step1./step.3roundup(step 4.,0)mod(step 5.-1,3)+1using index pattern
5=SEQUENCE(3^3)=F6#/H16#=ROUNDUP(N6#,0)=MOD(R6#-1,3)+1=INDEX(B4:D4,V6#)
610.11110.33331111111AAA
7tot obj.=r=32step 2. 0.22220.66672112112AAB
8nr.chosen=c=33count clm from right to left -10.333313113113AAC
94c-sequence(,c)0.44441.33334124121ABA
10tot perm =r^c5=3-SEQUENCE(,3)0.55561.66675125122ABB
11=3^362100.666726126123ABC
122770.77782.33337137131ACA
1380.88892.66678138132ACB
14=PERMUTATIONA(3,3)9step 3. r^step 2.139139133ACC
152710=3^H11#1.11113.3333102410211BAA
16119311.22223.6667112411212BAB
17121.33334122412213BAC
18131.44444.3333132513221BBA
19141.55564.6667142514222BBB
20151.66675152515223BBC
21161.77785.3333162616231BCA
22171.88895.6667172617232BCB
231826182618233BCC
24192.11116.3333193719311CAA
25202.22226.6667203720312CAB
26212.33337213721313CAC
27222.44447.3333223822321CBA
28232.55567.6667233823322CBB
29242.66678243824323CBC
30252.77788.3333253925331CCA
31262.88898.6667263926332CCB
322739273927333CCC
33
ACOMB 1
Cell Formulas
RangeFormula
F5,H15,B14,B11,H10,R5,V5,Z5,N5F5=FORMULATEXT(F6)
F6:F32F6=SEQUENCE(3^3)
N6:P32N6=F6#/H16#
R6:T32R6=ROUNDUP(N6#,0)
V6:X32V6=MOD(R6#-1,3)+1
Z6:AB32Z6=INDEX(B4:D4,V6#)
H11:J11H11=3-SEQUENCE(,3)
B12B12=3^3
B15B15=PERMUTATIONA(3,3)
H16:J16H16=3^H11#
Dynamic array formulas.
 
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.
 
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 !! 🙏✌️
 
@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, 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.
 
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.
 
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. ✌️
 

Forum statistics

Threads
1,223,604
Messages
6,173,319
Members
452,510
Latest member
RCan29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top