Finding crew pairing possibilities

Question

Group BLUE can be paired with another member of group BLUE or with a member of RED

BUT Group RED can only be paired with a member of group BLUE

BLUE	RED
Nik	Steve
Pat	Alex

Nik & Steve

Nik & Alex

Nik & Pat

Pat & Steve

Pat & Alex

This is 5 crew pairings (Nik/Pat is the same as Pat/Nik, so it should be counted only one time). How would I write the formula to allow me to continue to build by continuing adding members to both groups? I tried =Blue((blue-1)red) which does not yield an accurate total.

Answer 1

This sounds more amenable to a SQL solution, so I would describe your blue table and your red table to POWERQUERY in Excel then build a query like this:

Select b1.name as PairFirstPart, b2.name as PairSecondPart
From TableBlue b1 Inner Join TableBlue b2 ON
     b1.name<b2.name
UNION ALL
Select b.name, r.name
From TableBlue b Inner Join TableRed r

Now you can add elements to both tables and then re-run your query for updated results.

Answer 2

I agree that your problem could be solved with Power Query quite easily, but if you want a formula, with Office 365, you can do:

=LET( pilots, A2:B6,
        genPerms, LAMBDA(matrix,
                     LET( A, matrix,  B,ROWS(A),
                          C,COLUMNS(A), D,IF(A="", NA(),A),
                          E,MAKEARRAY(B,C,LAMBDA(rw,cl,INDEX(SORT(INDEX(D,0,cl)),rw))),
                          F,BYCOL(E,LAMBDA(cl,COUNTA(FILTER(cl,NOT(ISERROR(cl)))))),
                          G,MAKEARRAY(PRODUCT(F),C,LAMBDA(rw,cl,INDEX(E,MOD(CEILING(rw/IFERROR(PRODUCT(INDEX(F,SEQUENCE(C-cl,,cl+1))),1),1)-1,INDEX(F,cl))+1,cl))),
                          UNIQUE(G)) ),
        vs, LAMBDA(array1,array2,
               LET( rows1, ROWS( array1 ), rows2, ROWS( array2 ),
                    columns1, COLUMNS( array1 ), columns2, COLUMNS( array2 ),
                    rSeq, SEQUENCE( rows1 + rows2 ),
                    cSeq, SEQUENCE(, MAX( columns1, columns2 ) ),
                    IF( ISOMITTED(array1),
                        array2,
                        IF( ISOMITTED(array2),
                            array1,
                            IF( rSeq <= rows1,
                                INDEX( IF( array1 = "", "", array1), rSeq, cSeq ),
                                INDEX( IF( array2 = "", "", array2), rSeq-rows1, cSeq ) ) ) ) ) ),
        bluePilots, genPerms( CHOOSE( {1,2}, IF( ISBLANK(INDEX( pilots,,1)),"",INDEX( pilots,,1)),IF( ISBLANK(INDEX( pilots,,1)),"",INDEX( pilots,,1)) ) ),
        vs( genPerms(pilots), FILTER( bluePilots, INDEX( bluePilots,,1) < INDEX( bluePilots,,2) ) ) )

where A2:B6 is the array of pilots on Blue and Red. This is really heavy, but it is because I just banged together already built and tested formulas that generate all permutations (using the method from JvdV) and then modified them to accommodate the Blue on Blue situation. A much leaner approach is possible, but would require a lot of effort and testing and perhaps genius I don't possess.

I also purposefully avoided making registered LAMBDA's because it would require a long explanation.

Office Insiders Approach

If you are part of the Office Insiders program you can use the HSTACK and VSTACK which will radically reduce the bloat of this formula:

=LET( pilots, A2:B6,
        genPerms, LAMBDA(matrix,
                     LET( A, matrix,  B,ROWS(A),
                          C,COLUMNS(A), D,IF(A="", NA(),A),
                          E,MAKEARRAY(B,C,LAMBDA(rw,cl,INDEX(SORT(INDEX(D,0,cl)),rw))),
                          F,BYCOL(E,LAMBDA(cl,COUNTA(FILTER(cl,NOT(ISERROR(cl)))))),
                          G,MAKEARRAY(PRODUCT(F),C,LAMBDA(rw,cl,INDEX(E,MOD(CEILING(rw/IFERROR(PRODUCT(INDEX(F,SEQUENCE(C-cl,,cl+1))),1),1)-1,INDEX(F,cl))+1,cl))),
                          UNIQUE(G)) ),
        bluePilots, genPerms( HSTACK(INDEX( pilots,,1),INDEX( pilots,,1)) ),
        VSTACK( genPerms(pilots), FILTER( bluePilots, INDEX( bluePilots,,1) < INDEX( bluePilots,,2) ) ) )

Answer 3

I wondered if you could actually implement @Chris Maurer's answer directly in Excel.

Yes you can basically follow the instructions here (Data | Get data | From other sources | Microsoft query) and edit the SQL, but I could only make it work by putting the Blue table in one sheet which I have named Blue and the Red table in another sheet which I have named Red:

and

Then the SQL looked like this:

SELECT t1.Person , t2.Person 
FROM `C:\Users\myID\OneDrive\Documents\Excel\Source.xlsx`.`Blue$` t1,
 `C:\Users\myID\OneDrive\Documents\Excel\Source.xlsx`.`Red$` t2
union all
SELECT t1.Person, t2.Person
FROM `C:\Users\myID\OneDrive\Documents\Excel\Source.xlsx`.`Blue$` t1
inner join
 `C:\Users\myID\OneDrive\Documents\Excel\Source.xlsx`.`Blue$` t2
on t1.person<t2.person

Resulting in

See also

Answer 4

Another Excel option:

=LET(β,A2:A5,ρ,B2:B5,ζ,ROWS(β),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,β&" and "&TRANSPOSE(ρ),IF(SEQUENCE(ζ)<SEQUENCE(,ζ),β&" and "&TRANSPOSE(β),""))&"</b></a>","//b"))