Home » Pair Semi-Final Cricket Teams

Pair Semi-Final Cricket Teams

This challenge is inspired from ongoing T20 Cricket World cup. Given are two tables of teams, Group A and Group B (This is real data taken from cricinfo.com). Top 2 teams from each group will qualify for semi-finals. Ranking is decided on the basis of points. If there is a tie in points, then NRR will decide the ranking. (Higher points, Higher NRR means Higher rank) 1st ranked team of Group A will play 2nd ranked team of Group B in Semi Final 1. 2nd ranked team of Group A will play 1st ranked team of Group B in Semi Final 2. Provide a formula to pair up teams for Semi Finals.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 62
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Pair Semi-Final Cricket Teams with Power Query

Power Query solution 1 for Pair Semi-Final Cricket Teams, proposed by Matthias Friedmann: 
let
 A = Excel.CurrentWorkbook(){[Name = "CricketA"]}[Content], 
 B = Excel.CurrentWorkbook(){[Name = "CricketB"]}[Content], 
 Sorted = Table.FromColumns(
 { Table.FirstN(Table.Sort(A, {{"Points", Order.Descending}, {"NRR", Order.Descending}}), 2)[Group A] } & 
 { Table.LastN(Table.Sort(B, {{"Points", Order.Ascending}, {"NRR", Order.Ascending}}), 2)[Group B] }, 
 {"Team1", "Team2"}
 )
in
 Sorted

Here are some explanations on this solution: https://www.linkedin.com/posts/matthiasfriedmann_excel-excelchallenge-powerquerychallenge-activity-6996457686598852609-vQNV
Power Query solution 2 for Pair Semi-Final Cricket Teams, proposed by Abdoul Karim N.: 
let
  Source = Excel.CurrentWorkbook(){[Name = "GroupA"]}[Content], 
  ChangedType = Table.TransformColumnTypes(
    Source, 
    {{"Group A", type text}, {"Points", Int64.Type}, {"NRR", type number}}
  ), 
  SortedRows = Table.Sort(ChangedType, {{"Points", Order.Descending}, {"NRR", Order.Descending}}), 
  First2GroupA = Table.FirstN(SortedRows, 2), 
  DemotedHeaders = Table.DemoteHeaders(First2GroupA), 
  Appended = Table.Combine({DemotedHeaders, GroupB}), 
  RemoveRowsHeaders = Table.SelectRows(Appended, each ([Column2] <> "Points")), 
  RemovedColumns = Table.RemoveColumns(RemoveRowsHeaders, {"Column2", "Column3"}), 
  Team1 = List.Alternate(RemovedColumns[Column1], 1, 1, 1), 
  Team2 = List.Alternate(RemovedColumns[Column1], 1, 1, 0), 
  GetTable = Table.FromColumns({Team1, Team2}), 
  RenamedColumns = Table.RenameColumns(GetTable, {{"Column1", "Team1"}, {"Column2", "Team2"}})
in
  RenamedColumns

Solving the challenge of Pair Semi-Final Cricket Teams with Excel

Excel solution 1 for Pair Semi-Final Cricket Teams, proposed by Rick Rothstein: 
=HSTACK(TAKE(SORT(A2:C7,{2,3},-1),2,1),TAKE(SORT(E2:G7,{2,3}),-2,1))
Excel solution 2 for Pair Semi-Final Cricket Teams, proposed by John V.: 
=INDEX(VSTACK(SORT(A2:C7,{2,3}),SORT(E2:G7,{2,3})),{6,11;5,12})
Excel solution 3 for Pair Semi-Final Cricket Teams, proposed by محمد حلمي: 
=HSTACK(
TAKE(SORT(A2:C7,{2,3},-1),2,1),
TAKE(SORT(TAKE(SORT(E2:G7,{2,3},-1),2),{2,3}),,1))
Excel solution 4 for Pair Semi-Final Cricket Teams, proposed by محمد حلمي: 
=HSTACK(
TAKE(SORT(A2:C7,{2,3},-1),2,1),
TAKE(SORT(TAKE(SORT(E2:G7,{2,3},-1),2,2),2),2,1))
Excel solution 5 for Pair Semi-Final Cricket Teams, proposed by 🇰🇷 Taeyong Shin: 
=LET(Apoi, B2:B7+C2:C7/10, Bpoi, F2:F7+G2:G7/10,
HSTACK( XLOOKUP(LARGE(Apoi, {1;2}), Apoi, A2:A7),
 XLOOKUP(LARGE(Bpoi, {2;1}), Bpoi, E2:E7) )
)
Excel solution 6 for Pair Semi-Final Cricket Teams, proposed by Kris Jaganah: 
=LET(a,A2:A7,b,B2:B7+C2:C7/1000,d,FILTER(a,b=LARGE(b,1)),e,FILTER(a,b=LARGE(b,2)),f,E2:E7,g,F2:F7+G2:G7/1000,h,FILTER(f,g=LARGE(g,1)),i,FILTER(f,g=LARGE(g,2)),HSTACK(VSTACK("","Semi Final1","Semi Final2"),VSTACK("Team 1",d,e),VSTACK("Team 2",i,h)))
Excel solution 7 for Pair Semi-Final Cricket Teams, proposed by Timothée BLIOT: 
=LET(GroupA, A2:C7, GroupB, E2:G7,
RankA, SORT(GroupA,{2,3},-1), RankB, SORT(GroupB,{2,3},-1),

HSTACK(INDEX(RankA,,1),INDEX(RankB,,1)))
Excel solution 8 for Pair Semi-Final Cricket Teams, proposed by Charles Roldan: 
=LET( a, A2:C7, b, E2:G7,
f, LAMBDA(x, TAKE( SORT( x, {2,3}, {-1,-1}), , 1)),
HSTACK( INDEX( f( a), {1;2}), INDEX( f( b), {2;1})))
Excel solution 9 for Pair Semi-Final Cricket Teams, proposed by Stefan Olsson: 
={QUERY(
    {A2:C7},
    "SELECT Col1 ORDER BY Col2 DESC, Col3 DESC LIMIT 2"
),
    QUERY(
        {E2:G7},
        "SELECT Col1 ORDER BY Col2, Col3 LIMIT 2 offset 4"
    )
Excel solution 10 for Pair Semi-Final Cricket Teams, proposed by Stefan Olsson: 
=LAMBDA(_A,_B,_q1,{QUERY({_A},_q1,0),QUERY({_B},_q1&" offset 1",0);QUERY({_A},_q1&" offset 1",0),QUERY({_B},_q1,0)})(A2:C7,D2:F7,"select Col1 order by Col2 desc, Col3 desc limit 1")
Excel solution 11 for Pair Semi-Final Cricket Teams, proposed by Oscar Javier Rosero Jiménez: 
=HSTACK(CHOOSEROWS(SORTBY(A2:A7,C2:C7,-1),1,2),CHOOSEROWS(SORTBY(E2:E7,G2:G7,-1),2,1))
Excel solution 12 for Pair Semi-Final Cricket Teams, proposed by Victor Momoh (MVP, MOS, R.Eng): 
=HSTACK(TAKE(SORTBY(A2:A7,B2:B7,-1,C2:C7,-1),2),TAKE(SORTBY(E2:E7,F2:F7,1,G2:G7,1),-2))
Excel solution 13 for Pair Semi-Final Cricket Teams, proposed by Paolo Pozzoli: 
=LET(
    
    groupA;
    A2:C7;
    
    groupB;
    E2:G7;
    
    top2A;
    SCEGLI.RIGA(
        SCEGLI.COL(
            DATI.ORDINA.PER(
                groupA;
                SCEGLI.COL(
                    groupA;
                    3
                );
                -1
            );
            1
        );
        SEQUENZA(
            2
        )
    );
    
    top2B;
    SCEGLI.RIGA(
        SCEGLI.COL(
            DATI.ORDINA.PER(
                groupB;
                SCEGLI.COL(
                    groupB;
                    3
                );
                -1
            );
            1
        );
        SEQUENZA(
            2;
            ;
            2;
            -1
        )
    );
    
    semifinal;
    STACK.ORIZ(
        top2A;
        top2B
    );
    
    semifinal
)
Excel solution 14 for Pair Semi-Final Cricket Teams, proposed by RIJESH T.: 
=LET(a,TAKE(SORT(A2:C7,{2,3},-1),2,1),b,SORT(TAKE(SORT(E2:G7,{2,3},-1),2,1),,-1),VSTACK("Team"&SEQUENCE(,2),HSTACK(a,b)))
Excel solution 15 for Pair Semi-Final Cricket Teams, proposed by Ibrahim Sadiq: 
=HSTACK(INDEX(SORT(A2:C7,{2,3},-1),{1;2}),INDEX(SORT(E2:G7,{2,3},-1),{2;1}))
Excel solution 16 for Pair Semi-Final Cricket Teams, proposed by Stevenson Yu: 
=HSTACK(DROP(SORTBY(A2:A7,C2:C7,-1),-4),DROP(SORTBY(E2:E7,G2:G7),4))
Excel solution 17 for Pair Semi-Final Cricket Teams, proposed by Paul Reynolds: 
=HSTACK(VSTACK("Semi Final"&{1;2}),TAKE(SORTBY(A2:A7,B2:B7,-1,C2:C7,-1),2),TAKE(SORTBY(E2:E7,F2:F7,1,G2:G7,1),-2))

I also wasn't sure if we could assume NRR and Points ranking would line up.
Again, thanks for doing these.  I really enjoying going through these.  Anytime my solution is the same as Victor Momoh (MVP, MOS), I call that a huge win.  ;-)

Leave a Reply