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.

CodePudding user response：

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"))

CodePudding user response：

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.

CodePudding user response：

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

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) ) ) )

CodePudding user response：

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

Yes you can basically follow the instructions

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