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!