Finding crew paring possibilities-CodePudding

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：

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:B3,
        genPerms, LAMBDA(matrix,
                     LET( cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ),
                          rC, ROWS( matrix ), rSeq, SEQUENCE( rC ),
                          eC, rC ^ cC, eSeq, SEQUENCE( eC,,0 ),
                          unblank, IF( ISBLANK(matrix), "°|°", matrix ),
                          m, UNIQUE( INDEX( unblank, MOD( INT( INT( SEQUENCE( eC, cC, 0 )/cC )/rC^SEQUENCE( 1, cC, cC-1, -1 ) ), rC )   1, cSeq ) ),
                          FILTER( m, BYROW( IFERROR( FIND( "°|°", m ), 0 ), LAMBDA(x, SUM( x ) ) ) = 0 ) ) ),
        hs, LAMBDA(array1,array2,
               LET( rows1, ROWS( array1 ), rows2, ROWS( array2 ),
                    columns1, COLUMNS( array1 ), columns2, COLUMNS( array2 ),
                    rSeq, SEQUENCE( MAX( rows1, rows2 ) ),
                    cSeq, SEQUENCE(, columns1   columns2 ),
                    IF( ISOMITTED(array1),
                        array2,
                        IF( ISOMITTED(array2),
                            array1,
                            IF( cSeq <= columns1,
                                INDEX( IF( array1 = "", "", array1), rSeq, cSeq ),
                                INDEX( IF( array2 = "", "", array2), rSeq, cSeq-columns1 ) ) ) ) ) ),
        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( hs(INDEX( pilots,,1),INDEX( pilots,,1)) ),
        vs( genPerms(pilots), FILTER( bluePilots, INDEX( bluePilots,,1) < INDEX( bluePilots,,2) ) ) )

where A2:B3 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:B3,
        genPerms, LAMBDA(matrix,
                     LET( cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ),
                          rC, ROWS( matrix ), rSeq, SEQUENCE( rC ),
                          eC, rC ^ cC, eSeq, SEQUENCE( eC,,0 ),
                          unblank, IF( ISBLANK(matrix), "°|°", matrix ),
                          m, UNIQUE( INDEX( unblank, MOD( INT( INT( SEQUENCE( eC, cC, 0 )/cC )/rC^SEQUENCE( 1, cC, cC-1, -1 ) ), rC )   1, cSeq ) ),
                          FILTER( m, BYROW( IFERROR( FIND( "°|°", m ), 0 ), LAMBDA(x, SUM( x ) ) ) = 0 ) ) ),
        bluePilots, genPerms( HSTACK(INDEX( pilots,,1),INDEX( pilots,,1)) ),
        VSTACK( genPerms(pilots), FILTER( bluePilots, INDEX( bluePilots,,1) < INDEX( bluePilots,,2) ) ) )