Tournament Brackets

[upmyallee]

I'm trying to make tournament brackets of individuals in different teams. I am trying to randomize the brackets to start, and have been able to do so by using =rand() function along with vlookups. The only issue, is sometimes randomizing matches certain teammates up together in the first round. Is there a way to randomize it with stipulation that team members do not get paired together at first?

It is a list of 32 players, and each pair can not be from the same team to start.

 

[Legacy 456155]

Well, formulas are almost Greek to me, but this seemed interesting enough to give it a shot. It does seem to work!
Just press F9 to recalculate until "Ok" magically appears. This will generate a unique list with no consecutive Odd/Even pairs.
You could surely combine some of the steps into a single formula, but I thought it easier to understand by leaving the parts as is.

Book1
	A	B	C	D	E	F	G	H
1	1	1	Player 1, Pair 1	0.849495	6	3	FALSE	Ok
2	2	1	Player 2, Pair 1	0.995607	1	1	FALSE
3	3	2	Player 3, Pair 2	0.088461	30	15	FALSE
4	4	2	Player 4, Pair 2	0.532909	15	8	FALSE
5	5	3	Player 5, Pair 3	0.471921	19	10	FALSE
6	6	3	Player 6, Pair 3	0.344665	22	11	FALSE
7	7	4	Player 7, Pair 4	0.946508	4	2	FALSE
8	8	4	Player 8, Pair 4	0.818914	7	4	FALSE
9	9	5	Player 9, Pair 5	0.488377	16	8	FALSE
10	10	5	Player 10, Pair 5	0.583654	12	6	FALSE
11	11	6	Player 11, Pair 6	0.452161	20	10	FALSE
12	12	6	Player 12, Pair 6	0.671948	9	5	FALSE
13	13	7	Player 13, Pair 7	0.335062	23	12	FALSE
14	14	7	Player 14, Pair 7	0.881789	5	3	FALSE
15	15	8	Player 15, Pair 8	0.486449	17	9	FALSE
16	16	8	Player 16, Pair 8	0.102473	29	15	FALSE
17	17	9	Player 17, Pair 9	0.333087	24	12	FALSE
18	18	9	Player 18, Pair 9	0.241691	26	13	FALSE
19	19	10	Player 19, Pair 10	0.631173	10	5	FALSE
20	20	10	Player 20, Pair 10	0.040094	32	16	FALSE
21	21	11	Player 21, Pair 11	0.477171	18	9	FALSE
22	22	11	Player 22, Pair 11	0.321232	25	13	FALSE
23	23	12	Player 23, Pair 12	0.548122	14	7	FALSE
24	24	12	Player 24, Pair 12	0.95458	2	1	FALSE
25	25	13	Player 25, Pair 13	0.767943	8	4	FALSE
26	26	13	Player 26, Pair 13	0.121008	28	14	FALSE
27	27	14	Player 27, Pair 14	0.950535	3	2	FALSE
28	28	14	Player 28, Pair 14	0.39287	21	11	FALSE
29	29	15	Player 29, Pair 15	0.592773	11	6	FALSE
30	30	15	Player 30, Pair 15	0.070529	31	16	FALSE
31	31	16	Player 31, Pair 16	0.142784	27	14	FALSE
32	32	16	Player 32, Pair 16	0.577739	13	7	FALSE
Players

Cell Formulas
Range	Formula
D1:D32	D1	=RAND()
E1:E32	E1	=RANK(D1,$D$1:$D$32)
F1:F32	F1	=VLOOKUP(E1,$A$1:$B$32,2,FALSE)
G1:G32	G1	=IF(AND(ISODD(F1),COUNTIF($E$1:$E$32,E1)=1),E2-E1=1)
H1	H1	=IF(ISNA(MATCH(TRUE,G1:G32,0)),"Ok","Not Ok")

 

[upmyallee]

Just curious if there was a way to do it without having it to randomize until it all fits. We have teams of 3 people and some teams that don't get in it at all. I'll try to get the list of people and names together for test sample. This might work. I'll try it next week.

Thank you!

 

[Legacy 456155]

Please post back with whatever you come up with. I am curious. Thanks!

 

[lrobbo314]

How about this?

2020092015 MXL Bracket (Autosaved).xlsm
	A	B	C	D
1	Player	Team		Matchups
2	Player 1	Team 1		Team 3 - Player 1 vs Team 2 - Player 2
3	Player 2	Team 1		Team 3 - Player 2 vs Team 2 - Player 3
4	Player 3	Team 1		Team 3 - Player 3 vs Team 1 - Player 3
5	Player 1	Team 2		Team 4 - Player 1 vs Team 2 - Player 1
6	Player 2	Team 2		Team 4 - Player 2 vs Team 1 - Player 2
7	Player 3	Team 2		Team 4 - Player 3 vs Team 1 - Player 1
8	Player 1	Team 3
9	Player 2	Team 3
10	Player 3	Team 3
11	Player 1	Team 4
12	Player 2	Team 4
13	Player 3	Team 4
Sheet1

Sub BRACKETS()
Dim r As Range:         Set r = Range("Table1")
Dim AR() As Variant:    AR = r.Value2
Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")
Dim Grp1 As Object:     Set Grp1 = CreateObject("System.Collections.ArrayList")
Dim Grp2 As Object:     Set Grp2 = CreateObject("System.Collections.ArrayList")
Dim Res As Object:      Set Res = CreateObject("System.Collections.ArrayList")

For i = LBound(AR) To UBound(AR)
    If Not AL.contains(AR(i, 2)) Then AL.Add AR(i, 2)
Next i

StoG AL, Grp1, Grp2, AR()

For Each Plyr In Grp1
    RPOS = Application.WorksheetFunction.RandBetween(0, Grp2.Count - 1)
    OPP = Grp2.Item(RPOS)
    Res.Add Plyr & " vs " & OPP
    Grp2.Remove OPP
Next Plyr

Range("D2").Resize(Res.Count, 1) = Application.Transpose(Res.ToArray())

End Sub

Sub StoG(AL As Object, Grp1 As Object, Grp2 As Object, AR() As Variant)
Dim Pivot As Integer: Pivot = Int((AL.Count - 1) / 2)
Dim HF As Object: Set HF = CreateObject("System.Collections.ArrayList")
Dim r As Integer

For a = 0 To Pivot
    r = Application.WorksheetFunction.RandBetween(0, AL.Count - 1)
    HF.Add AL.Item(r)
    AL.Remove r
Next a

For i = LBound(AR) To UBound(AR)
    If HF.contains(AR(i, 2)) Then
        Grp1.Add Join(Array(AR(i, 2), AR(i, 1)), " - ")
    Else
        Grp2.Add Join(Array(AR(i, 2), AR(i, 1)), " - ")
    End If
Next i

End Sub

 

[upmyallee]

The code looks good to me only based upon my limited C# Coding class I took in college. However, I'm am seem to be a newbie when it comes to Excel. I'm getting an error that says "ActiveX component can't create object." I was able to figure out I believe that the first table needed to be named as Table1. But I can't seem to figure out what to do about AL.

Thank you

 

[lrobbo314]

Oh, I see you're on a Mac. That explains why the VBA failed. Also, Eww.

Either way here's a formula solution. It takes it bit of setup but works well.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Teams	ID	R	Rank	Group			Team	Player	Group	R	Rank	Link	OppTeam	OPP
2	Team 1	1	0.555895	2	0			Team 1	Player 1	0	0.463512	4	10	Team 4	Player 1
3	Team 2	2	0.585236	1	0			Team 1	Player 2	0	0.524299	3	9	Team 3	Player 3
4	Team 3	3	0.122781	4	1			Team 1	Player 3	0	0.019218	6	8	Team 3	Player 2
5	Team 4	4	0.188845	3	1			Team 2	Player 1	0	0.776985	1	11	Team 4	Player 2
6								Team 2	Player 2	0	0.774473	2	12	Team 4	Player 3
7								Team 2	Player 3	0	0.273625	5	7	Team 3	Player 1
8								Team 3	Player 1	1	0.03781	5	6	Team 2	Player 3
9								Team 3	Player 2	1	0.011885	6	3	Team 1	Player 3
10								Team 3	Player 3	1	0.087613	3	2	Team 1	Player 2
11								Team 4	Player 1	1	0.073577	4	1	Team 1	Player 1
12								Team 4	Player 2	1	0.533739	1	4	Team 2	Player 1
13								Team 4	Player 3	1	0.230658	2	5	Team 2	Player 2
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=ROWS($A$1:A1)
C2:C5,K2:K13	C2	=RAND()
D2:D5	D2	=RANK(C2,$C$2:$C$5)
E2:E5	E2	=IF(D2<INT(ROWS($A$2:$A$5)/2)+1,0,1)
J2:J13	J2	=INDEX($E$2:$E$5,MATCH(H2,$A$2:$A$5,0))
L2:L13	L2	=COUNTIFS($J$2:$J$13,J2,$K$2:$K$13,">"&K2)+1
M2:M13	M2	=MATCH(L2,IF($J$2:$J$13<>J2,$L$2:$L$13),0)
N2:O13	N2	=INDEX(H$2:H$13,$M2)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[lrobbo314]

Here is another way using Power Query.

Team Rank PQ.xlsx
	A	B	C	D	E	F
1	Teams		Teams	Players	Opp Team	Opp Player
2	Team 1		Team 1	Player 1	Team 5	Player 3
3	Team 2		Team 1	Player 2	Team 2	Player 1
4	Team 3		Team 1	Player 3	Team 16	Player 1
5	Team 4		Team 6	Player 1	Team 3	Player 1
6	Team 5		Team 6	Player 2	Team 4	Player 2
7	Team 6		Team 6	Player 3	Team 9	Player 1
8	Team 7		Team 7	Player 1	Team 11	Player 3
9	Team 8		Team 7	Player 2	Team 20	Player 2
10	Team 9		Team 7	Player 3	Team 14	Player 3
11	Team 10		Team 8	Player 1	Team 9	Player 2
12	Team 11		Team 8	Player 2	Team 20	Player 1
13	Team 12		Team 8	Player 3	Team 14	Player 1
14	Team 13		Team 10	Player 1	Team 5	Player 2
15	Team 14		Team 10	Player 2	Team 14	Player 2
16	Team 15		Team 10	Player 3	Team 2	Player 3
17	Team 16		Team 12	Player 1	Team 19	Player 3
18	Team 17		Team 12	Player 2	Team 9	Player 3
19	Team 18		Team 12	Player 3	Team 5	Player 1
20	Team 19		Team 13	Player 1	Team 4	Player 1
21	Team 20		Team 13	Player 2	Team 11	Player 2
22			Team 13	Player 3	Team 2	Player 2
23			Team 15	Player 1	Team 19	Player 1
24			Team 15	Player 2	Team 3	Player 2
25			Team 15	Player 3	Team 4	Player 3
26			Team 17	Player 1	Team 3	Player 3
27			Team 17	Player 2	Team 19	Player 2
28			Team 17	Player 3	Team 20	Player 3
29			Team 18	Player 1	Team 11	Player 1
30			Team 18	Player 2	Team 16	Player 3
31			Team 18	Player 3	Team 16	Player 2
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index0 = Table.AddIndexColumn(Source, "Index", 0, 1),
    RL = Table.FromList(List.Random(Table.RowCount(Index0)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Index1 = Table.AddIndexColumn(RL, "Index", 0, 1),
    Merge = Table.NestedJoin(Index0,{"Index"},Index1,{"Index"},"Added Index",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Added Index", {"Column1"}, {"Column1"}),
    Sort = Table.Sort(Expand,{{"Column1", Order.Ascending}}),
    Rank = Table.AddIndexColumn(Sort, "Rank", 0, 1),
    Sort1 = Table.Sort(Rank,{{"Index", Order.Ascending}}),
    RC = Table.RemoveColumns(Sort1,{"Index", "Column1"}),
    Groups = Table.AddColumn(RC, "Group", each if [Rank] < Int64.From(Table.RowCount(Sort1) / 2) then 0 else 1),
    GroupedRows = Table.Group(Groups, {"Group"}, {{"Count", each _, type table}}),
    gIndex = Table.TransformColumns(GroupedRows, {{"Count", each Table.AddIndexColumn(_, "Index", 0,1)}}),
    Players = Table.TransformColumns(gIndex, {{"Count", each Table.AddColumn(_, "Players", each {"Player 1", "Player 2", "Player 3"})}}),
    pExpand = Table.TransformColumns(Players, {{"Count", each Table.ExpandListColumn(_, "Players")}}),
    rNum = Table.Buffer(Table.TransformColumns(pExpand, {{"Count", (tbl) => Table.AddColumn(tbl, "R", (rl) => List.Random(Table.RowCount(tbl)){rl[Index]})}})),
    rSort = Table.TransformColumns(rNum, {{"Count", each Table.Sort(_, "R")}}),
    rRank = Table.TransformColumns(rSort, {{"Count", each Table.AddIndexColumn(_, "gRank", 0, 1)}}),
    Link = Table.NestedJoin(rRank{0}[Count], "gRank", rRank{1}[Count], "gRank", "Link"),
    ExpandLink = Table.ExpandTableColumn(Link, "Link", {"Teams", "Players"}, {"Opp Team", "Opp Player"}),
    ROC = Table.SelectColumns(ExpandLink,{"Teams", "Players", "Opp Team", "Opp Player"}),
    Dupe = Table.DuplicateColumn(ROC, "Teams", "Teams - Copy"),
    Split = Table.SplitColumn(Dupe, "Teams - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Teams - Copy.1", "Teams - Copy.2"}),
    Type = Table.TransformColumnTypes(Split,{{"Teams - Copy.1", type text}, {"Teams - Copy.2", Int64.Type}}),
    SortRows = Table.Sort(Type,{{"Teams - Copy.2", Order.Ascending}, {"Players", Order.Ascending}}),
    RC2 = Table.RemoveColumns(SortRows,{"Teams - Copy.1", "Teams - Copy.2"})
in
    RC2