ACOMBINE

=ACOMBINE(a,cl)

a
array
cl
array of column indexes, if ignored, all array will be considered, ex: {1,3} or {2,1} or {3,1,2}

list all possible combinations between all unique elements found on each column of an array

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. 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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1list 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 orderex.5=ACOMBINE(V3:X6,)
3ex.1=ACOMBINE(A4:B5,)ex.3=ACOMBINE(J4:L18,)=ACOMBINE(J4:L18,{1,3,2})MacwhiteEastMacblueEast
4A1A1BcxAaxAxaWinblueWestMacblueMidwest
5B2A2AaxAayAxbSamredMidwestMacblueWest
6B1BbxAbxAxcgreenMacgreenEast
7B2AcyAbyAyaMacgreenMidwest
8BbyAcxAybMacgreenWest
9ex.2=ACOMBINE(A10:C12,)BbxAcyAycMacredEast
10AaxAaxAayBaxBxaMacredMidwest
11BbyAayAcyBayBxbMacredWest
12cAbxBbyBbxBxcMacwhiteEast
13AbyAbxBbyByaMacwhiteMidwest
14AcxBaxBcxBybMacwhiteWest
15AcyAcyBcyBycSamblueEast
16BaxBbxSamblueMidwest
17BayAaySamblueWest
18BbxAaySamgreenEast
19BbySamgreenMidwest
20BcxFormula extracts unique values by columnsSamgreenWest
21Bcyand sorts them ascending top to bottom SamredEast
22=AUNIQUE(J4:L18,-1)=TRANSPOSE(AUNQSRT(TRANSPOSE(J23#),1))SamredMidwest
23ex.6 patterns=ACOMBINE(A24:C25,)BcxAaxSamredWest
24123121AayBbyother functions on minisheetSamwhiteEast
25321123bcAUNIQUESamwhiteMidwest
26321step1step2AUNQSRTSamwhiteWest
27323WinblueEast
28If same element can be found on more clmsWinblueMidwest
29will be repeated accordinglyWinblueWest
30=ACOMBINE(J31:K32,)WingreenEast
31xxx1WingreenMidwest
32y1xxWingreenWest
33y1WinredEast
34yxWinredMidwest
35WinredWest
36WinwhiteEast
37WinwhiteMidwest
38WinwhiteWest
39
ACOMBINE post
Cell Formulas
RangeFormula
Z2,E23,N22,E9,N3,R3,E3Z2=FORMULATEXT(Z3)
Z3:AB38Z3=ACOMBINE(V3:X6,)
E4:F7,N31:O34E4=ACOMBINE(A4:B5,)
N4:P15N4=ACOMBINE(J4:L18,)
R4:T15R4=ACOMBINE(J4:L18,{1,3,2})
E10:G21E10=ACOMBINE(A10:C12,)
I22,M30I22=FORMULATEXT(J23)
J23:L25J23=AUNIQUE(J4:L18,-1)
N23:P25N23=TRANSPOSE(AUNQSRT(TRANSPOSE(J23#),1))
E24:G27E24=ACOMBINE(A24:C25,)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,122
Members
452,381
Latest member
Nova88

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