Home » Generate Club Match Table

Generate Club Match Table

We had done a version of this challenge earlier. Column A contains a list of soccer clubs. Let’s say number of clubs is n. Create the table where each team face each other once only. A team should appear exactly (n-1)/2 times on one side of the result table in case of odd number of clubs. In case of even number of clubs, on one side it would appear n/2 times and on other side, it would appear n/2-1 times. You may not get the same order of teams in your output as shown.

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

Solving the challenge of Generate Club Match Table with Power Query

Power Query solution 1 for Generate Club Match Table, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AList = Table.TransformColumns(
    Table.AddIndexColumn(Source, "Team 2", 1, 1), 
    {
      "Team 2", 
      (r) =>
        let
          C = Source[Clubs], 
          N = List.Count(C), 
          M = Number.IntegerDivide(N, 2)
        in
          List.Range(C & C, r, (if r > M and N = M * 2 then M - 1 else M))
    }
  ), 
  Expanded = Table.ExpandListColumn(Table.RenameColumns(AList, {"Clubs", "Team 1"}), "Team 2")
in
  Expanded
Power Query solution 2 for Generate Club Match Table, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sorted = Table.Sort(Source, {{"Clubs", Order.Ascending}}), 
  Idx = Table.AddIndexColumn(Sorted, "Index", 1, 1, Int64.Type), 
  Equipo = Table.AddColumn(
    Idx, 
    "Team 2", 
    (x) =>
      if Number.IsOdd(Table.RowCount(Source)) then
        List.FirstN(
          List.Skip(List.Repeat(List.Sort(Idx[Clubs]), 2), x[Index]), 
          (Table.RowCount(Source) - 1) / 2
        )
      else
        [
          a = List.Skip(
            List.RemoveMatchingItems(List.Repeat(Idx[Clubs], b), {x[Clubs]}), 
            x[Index] + (if Number.IsOdd(b) then 1 else 0)
          ), 
          b = List.Count(Idx[Clubs]) / 2, 
          c = if Number.IsOdd(x[Index]) then b else b - 1, 
          d = if c = b then List.FirstN(a, b) else List.FirstN(a, b - 1)
        ][d]
  )[[Clubs], [Team 2]], 
  #"Team 2" = Table.ExpandListColumn(Equipo, "Team 2"), 
  Sol = Table.RenameColumns(#"Team 2", {{"Clubs", "Team 1"}})
in
  Sol
Power Query solution 3 for Generate Club Match Table, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Sorted = Table.Sort(Source,{{"Clubs", Order.Ascending}}),
 Idx = Table.AddIndexColumn(Sorted, "Index", 1, 1, Int64.Type),
 Equipo = Table.AddColumn(Idx, "Team 2", (x)=> List.FirstN(List.Skip(List.Sort(Source[Clubs])&List.Sort(Source[Clubs]), x[Index]), (Table.RowCount(Source)-1)/2) ),
 Expanded = Table.ExpandListColumn(Equipo, "Team 2")[[Clubs], [Team 2]],
 Sorted2 = Table.Sort(Expanded,{{"Clubs", Order.Ascending}, {"Team 2", Order.Ascending}}),
 Sol = Table.RenameColumns(Sorted2,{{"Clubs", "Team 1"}})
in
 Sol

Estoy viendo como incorporar en el query cuando son equipos pares, pero no he podido combinar 4 juegos por equipo y que los equipos no puedan repetir mas de 3 partidos.

Sigo intentando!!


                    
                  
          
            

  
                  
    
      
        Show translation
      
      
        Show translation of this comment
Power Query solution 4 for Generate Club Match Table, proposed by Alexis Olson: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Team1 = Table.RenameColumns(Source, {{"Clubs", "Team 1"}}), 
  Index1 = Table.AddIndexColumn(Team1, "Index 1", 0, 1, Int64.Type), 
  Matches = Table.AddColumn(
    Index1, 
    "Matches", 
    each 
      let
        matches = List.RemoveItems(Team1[Team 1], {[Team 1]}), 
        zip     = List.Zip({matches, List.Positions(matches)})
      in
        Table.FromRows(zip, {"Team 2", "Index 2"})
  ), 
  Expand = Table.ExpandTableColumn(Matches, "Matches", {"Team 2", "Index 2"}), 
  Filter = Table.SelectRows(Expand, each Number.Mod([Index 1] + [Index 2], 2) = 0)
in
  Filter
Power Query solution 5 for Generate Club Match Table, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData52"]}[Content], 
  Clubs = List.Sort(Source[Clubs]), 
  NbClubs = List.Count(Clubs), 
  Clubsx2 = List.Combine({Clubs, Clubs}), 
  NbToExtract = Number.RoundUp((NbClubs - 1) / 2), 
  MatchRecords = List.Accumulate(
    Clubs, 
    {}, 
    (s, c) =>
      s
        & {
          [
            T1 = c, 
            T2 = List.FirstN(List.Skip(Clubsx2, List.PositionOf(Clubsx2, c) + 1), NbToExtract)
          ]
        }
  ), 
  Expand = Table.ExpandListColumn(Table.FromRecords(MatchRecords), "T2"), 
  CheckDistinct = 
    let
      _AddCheck = Table.AddColumn(Expand, "Check", each Text.Combine(List.Sort({[T1], [T2]}), ";"))
    in
      Table.Distinct(_AddCheck, {"Check"})[[T1], [T2]], 
  Rename = Table.RenameColumns(CheckDistinct, {{"T1", "Team 1"}, {"T2", "Team 2"}}), 
  Sort = Table.Sort(Rename, {{"Team 1", Order.Ascending}, {"Team 2", Order.Ascending}})
in
  Sort
Power Query solution 6 for Generate Club Match Table, proposed by Matthias Friedmann: 
let
 GRTList = List.Generate( 
 ()=> [ Count = 1, i = 0 ],
 each [i] < List.Count(grouping),
 each try 
 if grouping{[i]} = grouping{[i] + 1}
 then [Count = [Count] + 1, i = [i] + 1]
 else [Count = 1, i = [i] + 1]
 otherwise [i = [i] + 1],
 each [Count]
 )
in
 GRTList
let
 Source = Excel.CurrentWorkbook(){[Name="Clubs"]}[Content][Clubs],
 count = List.Count(Source)-1,
 Team1 = List.Accumulate({0..count}, {}, (s,c) => s & List.Repeat({Source{c}}, List.Count(Source)-1-c) ),
 Team2 = List.Accumulate({0..count}, {}, (s,c) => s & List.Skip(Source,c+1) ),
 FromColumns = Table.FromColumns(
 {Team1} & {Team2},
 {"Team 1","Team 2"} 
 ),
 Custom1 = Table.FromColumns(
 Table.ToColumns(FromColumns) & {fxCountClubs( FromColumns[Team 1]) },
 Table.ColumnNames(FromColumns) & {"Count"}
 ),
 Custom2 = Table.Split(Custom1, 1),
 Custom3 = Table.Combine(List.Transform(Custom2, each if _[Count]{0} > Number.IntegerDivide(List.Count(Source),2) then Table.RenameColumns(_, {{"Team 1", "Team 2"}, {"Team 2", "Team 1"}}) else _ ))[[Team 1], [Team 2]]
in
 Custom3
Power Query solution 7 for Generate Club Match Table, proposed by Matthias Friedmann: 
let
 Source = Excel.CurrentWorkbook(){[Name="Clubs"]}[Content][Clubs],
 count = List.Count(Source)-1,
 Team1 = List.Accumulate({0..count}, {}, (s,c) => s & List.Repeat({Source{c}}, List.Count(Source)-1-c) ),
 Team2 = List.Accumulate({0..count}, {}, (s,c) => s & List.Skip(Source,c+1) )
in
 Table.FromColumns(
 {Team1} & {Team2},
 {"Team1","Team2"} 
 )

let
 Source = Excel.CurrentWorkbook(){[Name = "Clubs"]}[Content], 
 #"Renamed Columns" = Table.RenameColumns(Source, {{"Clubs", "Team1"}}), 
 #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type), 
 #"Added Custom" = Table.AddColumn(
 #"Added Index", 
 "Team2", 
 each List.Skip(#"Added Index"[Team 1], [Index])
 )[[Team1], [Team2]], 
 #"Expanded Team2" = Table.ExpandListColumn(#"Added Custom", "Team 2"), 
 #"Filtered Rows" = Table.SelectRows(#"Expanded Team2", each not ([Team2] = null))
in
 #"Filtered Rows"

Solving the challenge of Generate Club Match Table with Excel

Excel solution 1 for Generate Club Match Table, proposed by Bo Rydobon 🇹🇭: 
=LET(z,SORT(A2:A10),n,ROWS(z),m,INT(n/2),r,SEQUENCE(n),c,SEQUENCE(,m,0),x,(cm)*(n=m*2)),INDEX(z,HSTACK(TOCOL(IF(x,r,y),3),TOCOL(IF(x,MOD(r+c,n)+1,y),3))))
Excel solution 2 for Generate Club Match Table, proposed by محمد حلمي: 
=LET(
v,A2:A9,
r,ROWS(v),
a,SORT(v),
s,SEQUENCE(,r/2),
n,SEQUENCE(r),
m,n+s^0-1,
i,ISODD(r),
SORT(
HSTACK(
TOCOL(IF(i,IFNA(a,s),INDEX(a,TOCOL(IF(r/2>=n,m,DROP(m,,1)),2)))),
INDEX(a,TOCOL(MOD(IF(i,n,DROP(n,-1))+s-1,r)+1))),{1,2}))
Excel solution 3 for Generate Club Match Table, proposed by محمد حلمي: 
=LET(
v,A2:A9,
r,ROWS(v),
a,SORT(v),
s,SEQUENCE(,r/2),
n,SEQUENCE(r),
m,n+s^0-1,
i,ISODD(r),
SORT(
HSTACK(
TOCOL(IF(i,IFNA(a,s),INDEX(a,TOCOL(IF(r/2>=n,m,DROP(m,,1)),2)))),
INDEX(a,TOCOL(MOD(IF(i,n,DROP(n,-1))+s-1,r)+1))),{1,2}))
Excel solution 4 for Generate Club Match Table, proposed by 🇰🇷 Taeyong Shin: 
=LET(
 sort, SORT(A2:A10),
 stack, VSTACK(sort, sort),
 n, ROWS(sort),
 d, INT(n / 2),
 seq, SEQUENCE(n),
 k, SORTBY(seq, ISEVEN(seq)),
 str, MAP(sort, k, seq, LAMBDA(m,r,s,
 LET(
 num, d - (ISEVEN(n) * ISEVEN(r)),
 t_1, EXPAND("", num, , ) & m,
 t_2, TAKE(DROP(stack, s), num),
 c, CONCAT(t_1 & ", " & t_2 & ";"),
 LEFT(c, LEN(c) - 1)
 )
 )),
 TEXTSPLIT(TEXTJOIN(";", , str), ", ", ";")
)
Excel solution 5 for Generate Club Match Table, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
 _d, A2:A10,
 _fd, SORT(_d),
 _fsd, VSTACK(_fd, _fd),
 _rw, ROWS(_d),
 _frw, INT(_rw / 2),
 _sq, SEQUENCE(_rw * _frw, , 0),
 _qt, QUOTIENT(_sq, _frw) + 1,
 _md, MOD(_sq, _frw) + 1,
 _t1, INDEX(_fd, _qt),
 _t2, MAP(_qt, _md, LAMBDA(a, b, INDEX(DROP(_fsd, a), b))),
 _r, HSTACK(_t1, _t2),
 _r
)
Excel solution 6 for Generate Club Match Table, proposed by Hussein SATOUR: 
=LET(
c, A2:A10, a, COUNTA(c), b, ROUNDDOWN(a / 2, 0),
d, SEQUENCE(, a), e, SEQUENCE(b),
f, TOCOL(IFNA(d, e)), g, SEQUENCE(a * b),
h, f + ROUNDUP(g / a, 0) - IF(f + ROUNDUP(g / a, 0) > a, a, 0),
DROP(INDEX(c, HSTACK(f, h)), -ISEVEN(a) * b))

&&&

Leave a Reply