All Unique Combinations from 5 Columns of Data + Customizations - Excel 365

[Excel Jason]

I have a formula-driven table that generates all unique combinations from five columns (Core Output Table), which works correctly. However, I need to include custom combinations and integrate their outputs into the same table, regardless of their position. Attempts to adjust the formulas or use helper columns have not achieved this. See the attached image for reference.

Values in the "Custom Inputs Table" must generate all possible combinations alongside the other values in the Core Inputs table. Custom combinations should calculate row-wise using adjacent cells only. For instance, if Custom Input Location Code is US-001 and Person Eating is Brittany, the output includes only this pairing, excluding other custom rows (H5:I5 in this example) (e.g., US-002 and Frank). Combinations should be derived using data from the remaining 3 Core Inputs Table columns (Location Name, Desired Meal, and Contact Type).

Current Formulas in K4:O4 (Dragged Down) for the Core Outputs Table I'm looking to adjust to capture the above.

K4
=IF(ROW()-3<=COUNTA(B$4:B$16)*COUNTA(C$4:C$16)*COUNTA(D$4:D$16)*COUNTA(E$4:E$16)*COUNTA(F$4:F$16),
INDEX(B$4:B$16, MOD(INT((ROW()-4)/(COUNTA(C$4:C$16)*COUNTA(D$4:D$16)*COUNTA(E$4:E$16)*COUNTA(F$4:F$16))), COUNTA(B$4:B$16)) + 1),
"")
L4
=IF(ROW()-3<=COUNTA(B$4:B$16)*COUNTA(C$4:C$16)*COUNTA(D$4:D$16)*COUNTA(E$4:E$16)*COUNTA(F$4:F$16),
INDEX(C$4:C$16, MOD(INT((ROW()-4)/(COUNTA(D$4:D$16)*COUNTA(E$4:E$16)*COUNTA(F$4:F$16))), COUNTA(C$4:C$16)) + 1),
"")
M4
=IF(ROW()-3<=COUNTA(B$4:B$16)*COUNTA(C$4:C$16)*COUNTA(D$4:D$16)*COUNTA(E$4:E$16)*COUNTA(F$4:F$16),
INDEX(D$4:D$16, MOD(INT((ROW()-4)/(COUNTA(E$4:E$16)*COUNTA(F$4:F$16))), COUNTA(D$4:D$16)) + 1),
"")
N4
=IF(ROW()-3<=COUNTA(B$4:B$16)*COUNTA(C$4:C$16)*COUNTA(D$4:D$16)*COUNTA(E$4:E$16)*COUNTA(F$4:F$16),
INDEX(E$4:E$16, MOD(INT((ROW()-4)/COUNTA(F$4:F$16)), COUNTA(E$4:E$16)) + 1),
"")
O4
=IF(ROW()-3<=COUNTA(B$4:B$16)*COUNTA(C$4:C$16)*COUNTA(D$4:D$16)*COUNTA(E$4:E$16)*COUNTA(F$4:F$16),
INDEX(F$4:F$16, MOD(ROW()-4, COUNTA(F$4:F$16)) + 1),
"")

 

[djclements]

With Office 365, try the following single-cell array formula:

=LET(
    CrossJoinλ, LAMBDA(a,b,LET(i,SEQUENCE(ROWS(a)),j,SEQUENCE(,ROWS(b)),HSTACK(CHOOSEROWS(a,TOCOL(IF(j,i))),CHOOSEROWS(b,TOCOL(IF(i,j)))))),
    SplitColsλ, LAMBDA(a,BYCOL(a,LAMBDA(c,LET(x,IFERROR(TOCOL(c,1),""),LAMBDA(x))))),
    JoinByColλ, LAMBDA(a,REDUCE(INDEX(a,1)(),DROP(a,,1),LAMBDA(p,v,CrossJoinλ(p,INDEX(v,1)())))),
    split, SplitColsλ(B4:F16),
    join,  JoinByColλ(split),
    incl,  H4:H16<>"",
    IF(
        OR(incl),
        LET(
            add, BYROW(FILTER(H4:I16,incl),LAMBDA(r,LET(x,JoinByColλ(CHOOSECOLS(HSTACK(SplitColsλ(r),split),1,4,5,2,7)),LAMBDA(x)))),
            one, INDEX(add,1,1)(),
            VSTACK(IF(ROWS(add)=1,one,REDUCE(one,DROP(add,1),LAMBDA(p,v,VSTACK(p,INDEX(v,1)())))),join)
        ),
        join
    )
)

 

[djclements]

Revised version with improved code:

=LET(
    CrossJoinλ, LAMBDA(a,λ,LET(b,λ(),i,SEQUENCE(ROWS(a)),j,SEQUENCE(,ROWS(b)),HSTACK(CHOOSEROWS(a,TOCOL(IF(j,i))),CHOOSEROWS(b,TOCOL(IF(i,j)))))),
    SplitColsλ, LAMBDA(a,BYCOL(a,LAMBDA(c,LET(x,IFERROR(TOCOL(c,1),""),LAMBDA(x))))),
    JoinByColλ, LAMBDA(v,REDUCE((@v)(),DROP(v,,1),CrossJoinλ)),
    split, SplitColsλ(B4:F16),
    core,  JoinByColλ(split),
    incl,  H4:H16<>"",
    IF(
        OR(incl),
        LET(
            add, BYROW(FILTER(H4:I16,incl),LAMBDA(r,LET(x,JoinByColλ(CHOOSECOLS(HSTACK(SplitColsλ(r),split),1,4,5,2,7)),LAMBDA(x)))),
            one, INDEX(add,1,1)(),
            VSTACK(IF(ROWS(add)=1,one,REDUCE(one,DROP(add,1),LAMBDA(a,λ,VSTACK(a,λ())))),core)
        ),
        core
    )
)

Also, the final IF statement can be simplified, if you don't mind appending the custom combinations to the bottom of the core output table:

=LET(
    CrossJoinλ, LAMBDA(a,λ,LET(b,λ(),i,SEQUENCE(ROWS(a)),j,SEQUENCE(,ROWS(b)),HSTACK(CHOOSEROWS(a,TOCOL(IF(j,i))),CHOOSEROWS(b,TOCOL(IF(i,j)))))),
    SplitColsλ, LAMBDA(a,BYCOL(a,LAMBDA(c,LET(x,IFERROR(TOCOL(c,1),""),LAMBDA(x))))),
    JoinByColλ, LAMBDA(v,REDUCE((@v)(),DROP(v,,1),CrossJoinλ)),
    split, SplitColsλ(B4:F16),
    core,  JoinByColλ(split),
    incl,  H4:H16<>"",
    IF(
        OR(incl),
        REDUCE(
            core,
            BYROW(FILTER(H4:I16,incl),LAMBDA(r,LET(x,JoinByColλ(CHOOSECOLS(HSTACK(SplitColsλ(r),split),1,4,5,2,7)),LAMBDA(x)))),
            LAMBDA(a,λ,VSTACK(a,λ()))
        ),
        core
    )
)

 

[Excel Jason]

I'm thoroughly impressed, thank you very much!

 

[Excel Jason]

Would you be willing to help adjust the formula to function the same but remove the first column, i.e. create combination using only 4 columns instead of 5? See attached image.

Thank You!

 

[djclements]

No problem. You would just need to modify the col_nums arguments of CHOOSECOLS from:

CHOOSECOLS(HSTACK(SplitColsλ(r),split),1,4,5,2,7)

to:

CHOOSECOLS(HSTACK(SplitColsλ(r),split),1,4,2,6)

Basically, HSTACK joins the two tables together horizontally, with the custom table positioned to the left of the core table. So, with only 4 fields in the core table, the new field positions would look like this:

1​	2​	3​	4​	5​	6​
Location Name (custom)	Person Eating (custom)	Location Name (core)	Desired Meal (core)	Person Eating (core)	Contact Type (core)

Then, CHOOSECOLS rearranges the position of each field by replacing/omitting the two core fields (3 & 5) with the two custom fields (1 & 2), so you're left with:

1​	4​	2​	6​
Location Name (custom)	Desired Meal (core)	Person Eating (custom)	Contact Type (core)

Cheers!

 

[Excel Jason]

Thank You!