Single List - Create Two List Without Duplicates

[NastyWetSmear]

Good morning, experts.

I have a single list of people:

I need to create a list which:

1) Matches these people up for competition
2) Does so based on their "Yes" or "No" for attendance.
3) Ensures that the same person isn't facing themselves in competition.
4) Ensures that the same person isn't competing back to back.
5) Ensures that everyone on the list appears at least once.

So, for example, this would be no good: Amanda can't compete back to back

And this is no good: Terry can't compete against himself, and Sarah doesn't get to compete at all:

For the life of me I can't think of a formula that could take a single list and create two lists, checking against each other to ensure that every name on the list appears, but also there are no duplicates and the same name doesn't appear on either side of the list back to back.

Help?

 

[bebo021999]

1- Do you need that list to be random list?
Sam-Amanda or Sam-Terry, Luke-Terry... in random way
2- Or in this way: 1st Yes vs 2nd Yes (Sam vs Amanda), then 3rd Yes vs 4th Yes (Terry vs Luke)
3- How many players actually do you have?

 

[NastyWetSmear]

1- Do you need that list to be random list?
Sam-Amanda or Sam-Terry, Luke-Terry... in random way
2- Or in this way: 1st Yes vs 2nd Yes (Sam vs Amanda), then 3rd Yes vs 4th Yes (Terry vs Luke)
3- How many players actually do you have?

Thanks for taking a look.

At the moment we have 17, but it may change in the future.
It needs to be random, I'm afraid. The idea is to create a different layout of competition each time so the same matches aren't taking place each time.

I hope that helps.

 

[bebo021999]

VBA solution. Button click name "random matchs", to generate random list

Players.xlsm

drive.google.com

Below code:

Option Explicit
Sub randomMatches()
Dim lr&, i&, j&, k&, match&, rng, player(), yes(1 To 1000, 1 To 1), r
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "B").End(xlUp).Row
rng = Range("A2:C" & lr).Value
For i = 1 To UBound(rng)
    If rng(i, 3) = "Yes" Then
        k = k + 1: yes(k, 1) = rng(i, 2)
    End If
Next
match = Int(k / 2)
ReDim player(1 To match, 1 To 3)
Randomize
For i = 1 To match
    For j = 1 To 3
        If j = 2 Then
            player(i, j) = "vs"
        Else
            Do
                r = Rnd * (k - 1) + 1
                If Not dic.exists(yes(r, 1)) Then
                    dic.Add yes(r, 1), ""
                    player(i, j) = yes(r, 1)
                    Exit Do
                End If
            Loop
        End If
    Next
Next
Range("G2").Resize(match, 3).Value = player
dic.RemoveAll
End Sub

 

[NastyWetSmear]

Wow... Remarkable!
Would you be open to refining this a little more? Or have I already pushed my luck beyond the scope of random genius on the internet?

 

[NastyWetSmear]

VBA solution. Button click name "random matchs", to generate random list

Players.xlsm

drive.google.com

Below code:

Option Explicit
Sub randomMatches()
Dim lr&, i&, j&, k&, match&, rng, player(), yes(1 To 1000, 1 To 1), r
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "B").End(xlUp).Row
rng = Range("A2:C" & lr).Value
For i = 1 To UBound(rng)
    If rng(i, 3) = "Yes" Then
        k = k + 1: yes(k, 1) = rng(i, 2)
    End If
Next
match = Int(k / 2)
ReDim player(1 To match, 1 To 3)
Randomize
For i = 1 To match
    For j = 1 To 3
        If j = 2 Then
            player(i, j) = "vs"
        Else
            Do
                r = Rnd * (k - 1) + 1
                If Not dic.exists(yes(r, 1)) Then
                    dic.Add yes(r, 1), ""
                    player(i, j) = yes(r, 1)
                    Exit Do
                End If
            Loop
        End If
    Next
Next
Range("G2").Resize(match, 3).Value = player
dic.RemoveAll
End Sub

Wow... Remarkable!
Would you be open to refining this a little more? Or have I already pushed my luck beyond the scope of random genius on the internet?

 

[bebo021999]

Wow... Remarkable!
Would you be open to refining this a little more? Or have I already pushed my luck beyond the scope of random genius on the internet?

I always open for that.
What do you need now?

 

[NastyWetSmear]

I always open for that.
What do you need now?

Thanks again!

So, this is the current layout:

Your code works like a charm. I added a quick Macro that clears the lists, but I'm curious if I can also ask you to modify it to:

Check the Min number of matches and ensure there are at least that many.
Check the Max number of matches and ensure there are never more than this.
If neither form is filled out, just complete as normal with everyone competing at least once. Twice for someone if odd.

EG:

Bob, Sam and Terry are competing. In order for all three to have a match, Sam is randomly chosen to compete in round 1 and in round 3.


Obviously this means:

Some people will compete twice. That's fine, so long as they aren't competing against the same person again and they get a large break between competitions.
Some people will not get to compete when there are too few matches. That's fine, so long as the maximum number of people compete as possible.

What do you think?

 

[bebo021999]

Sorry I afraid I do not understand what you mean. Could you attach a sample sheet like I did before? And, manual input the desired results.

 

[NastyWetSmear]

Sorry I afraid I do not understand what you mean. Could you attach a sample sheet like I did before? And, manual input the desired results.

I can't upload the sheet, apparently, but here's some images:

So, normally, your code takes everyone who is a "Yes" and gives them all a single match if possible:

If it can be done, I'd like the list to react to the "Min Number Of Matches" and "Max Number Of Matches":
So, for example, if I tell it I need 8 matches, despite a perfect line up only having 5, it takes people who have had a break between matches and pits them against new people:

Or like in this example, where I only have a small number of people, so it makes sure the last match isn't a duplicate of a previous match, but also gives as much of a break between matches as possible:

Or, if I tell it I need a maximum of 4 matches, it creates only 4 matches:

Does that help?