Solved Finding matching pairs in excel using vba

February 25, 2016 at 11:50:04
Specs: Windows 10
I have 24 players playing billiards using 2-person teams. Thus 4 people are playing each other at any given time. There are 10 weeks of playing. I want to create a playing schedule such that duplicates are minimized over the 10 weeks. There are 3 pool tables so there is an early and late session to accommodate all players. (Added bonus: can all players play equally early and late sessions?)
Team AB playing team CD is the same as Team BA vs. Team DC.
I'm new to VBA but know enough to get by so give me your best shot.

message edited by chuckf201


See More: Finding matching pairs in excel using vba

Report •

✔ Best Answer
February 26, 2016 at 14:29:42
I just re-read my post and feel that I should clarify something.

I said:

The code creates a randomly sorted list of players for each week. e.g.

B, G, D, S, E, A, F, H, etc.

It then pairs those players based on that random order e.g.

BG, DS, EA, FH

In reality, the code only creates the randomly sorted lists of players for each week. It places those lists in C1:L24. It is the formulas in M1:V23 that actually create the pairs. Then the COUNTIF formulas in M25:V47 check for duplicates.

The COUNTIF formula in L26 then determines if there are any duplicates by checking to see if there are any values greater than 1 in the M25:V47 table.

It is the formula in L26 that the code monitors to determine if there needs to be a re-randomization of the current week. If anything other than 0 is in L26 after the current week's pairings are checked, then there must be at least one duplicate and the week must be rerun.

As I mentioned in my previous post, this process is not as clean as it could be. The worksheet is also not as pretty as it could be. I managed to come up with something that works even if it is a bit clunky. At that point I quit and posted what I had. Once you understand how it works, you should be able to make it look nice. If not, just come on back.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
February 25, 2016 at 12:54:38
So many questions...

re: "I have 24 players playing billiards using 2-person teams. Thus 4 people are playing each other at any given time."

Wouldn't that be 2 people playing 2 different people at any given time?

"4 people playing each other" sounds, at least to me, like more of a cut-throat game than 2 teams of 2.

re: There are 10 weeks of playing.

10 weeks @ once a week or 10 weeks @ 7 days a week or something different?

re: There are 3 pool tables so there is an early and late session to accommodate all players.

Are all 3 tables available for both the early and late sessions meaning 6 games per playing day?

Your use of the word "so" seems to indicate that it's the fact that you have 3 tables that allows for an early and late session. That doesn't make sense to me.

re: "Team AB playing team CD is the same as Team BA vs. Team DC."

Will A always play with B or do the players "rotate" amongst teams e.g.

AB, AC, AD...AX,

BC, BD, BE...BX

CD, CE, CF...CX

That's 276 possible teams. If each team is supposed to play every other team that's close to 38,000 combinations, if my math is correct.

I guess I need a little more clarification.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 25, 2016 at 13:20:34
Clarity is important.
2 people playing 2 people. Not cutthroat.
10 weeks at one a week.
Are all 3 tables available for both the early and late sessions meaning 6 games per playing day?. All three tables are available. Once early session ends late session starts. Each 4 players will play 7 games without changing teams or partners.

Will A always play with B or do the players "rotate" among teams e.g. ...
Players rotate so there is a mix of skill levels per team and is random, kind of.
Over the ten weeks I would like to see: (For Person A) AB:CD, AC:??, AD:??, etc
without duplicates.


Report •

#3
February 25, 2016 at 17:01:16
More clarity required...

10 play days x 2 sessions x 3 tables = 60 possible opportunities for play.

Is that correct?

re: "Each 4 players will play 7 games without changing teams or partners."

All in one session? e.g. AB:CD will 7 straight games during the Early Session of Week 1. After that A will never play with B again and C will never play with D again?

Is that correct?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 25, 2016 at 19:05:10
OK, I am going to assume that the assumptions I made in my previous post (#3) are correct.

With that said, I may have a solution for you, but first some key points:

1 - I did not write the core of this code. I found it on the web and then modified it to fit the requirements of this project. The original code came from Post # 4 of this thread:

http://www.mrexcel.com/forum/excel-...

2 - The code can be found in Module 1 of the workbook found at the link below, but I suggest that you read this entire post before you access the file. The code has to be run against a specific layout, so that it why you can't just paste it into your own workbook and expect it to run correctly. That said, it can certainly modified further to run against your own layout once you understand how it works.

3 - As shown, the final solution requires both the macro and the formulas in the spreadsheet. The reason for this is that it made it easier for me to modify and test the code by using both code and formulas in the spreadsheet to check for duplicates. If I felt like spending more times (hours!) on building all of that into the code, it could be done, but I'll leave that up to you if you so choose. It will be a great learning experience. ;-)

4 - I did not address this issue in the code:

Added bonus: can all players play equally early and late sessions?

It could be done, but let me explain why I didn't add it to the code.

The code creates a randomly sorted list of players for each week. e.g.

B, G, D, S, E, A, F, H, etc.

It then pairs those players based on that random order e.g.

BG, DS, EA, FH

So in Week 1, BG will play DS, EA will play FH, etc.

After the code creates the pairings for Week 2, it compares them to the Week 1 pairings to see if there are any duplicates. If there are any duplicates, it Randomizes the Week 2 pairings again (and again) until there are no duplicates.

After it creates the pairings for Week 3, it compares them to the Week 1 & 2 pairings to see if there are any duplicates. If there are any duplicates, it Randomizes the Week 3 pairings again (and again) until there are no duplicates.

That process continues all the way through Week 10. That is why you will see the code "slow down" as it gets deeper into the weeks. Since there are so many more chances for duplicates, more will be created and more re-randomizations will have to be done.

OK, so now imagine how long it would take if the code had to not only check for duplicates but also try to balance the Early and Late sessions across all players. Since the ordering is random, it could take a very, very long time.

However, I did not completely ignore that requirement. I included a table on the spreadsheet that performs a count showing how often each player ends up in Early session vs. the Late session. After you run the macro, if you don't like the distribution of Early vs. Late session for the players, just run the code again until you are satisfied.

5 - I do not know your level of VBA expertise, so I am going to make a suggestion. Years ago I wrote a tutorial explaining some basic VBA debugging techniques. You may be familiar with these techniques, you may not be. If you're not, I think you will find them very helpful in not only fixing code that doesn't work, but also in understanding code that does work. By Single Stepping and using Watches, you can see what the code is doing step-by-step as it runs. The tutorial can be found here:

http://www.computing.net/howtos/sho...

6 - OK, finally, the spreadsheet with the code...

When you open the workbook, it will be fairly blank, other than a list of players (A-X) and some text boxes. Once you run the code, the tables will be filled in and things will begin to make sense. I will completely understand it if doesn't make sense to you even after you run the code because I built it, not you. Please do not hesitate to come on back with specific questions and I'll answer them.

BTW, you do not have to clear the tables to run the code multiple times. If you do decide to clear any data, only clear the range with the random sets of single players. The other cells contain formulas that are required for the counting of the duplicates.

Here is the link. Have fun...I did!

http://www12.zippyshare.com/v/ulSbb...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
February 26, 2016 at 09:52:00
Thank you, thank you, THANK YOU. I will examine each line to understand the process.
Also, thanks for the links to help me becoming a better programmer.

Report •

#6
February 26, 2016 at 14:29:42
✔ Best Answer
I just re-read my post and feel that I should clarify something.

I said:

The code creates a randomly sorted list of players for each week. e.g.

B, G, D, S, E, A, F, H, etc.

It then pairs those players based on that random order e.g.

BG, DS, EA, FH

In reality, the code only creates the randomly sorted lists of players for each week. It places those lists in C1:L24. It is the formulas in M1:V23 that actually create the pairs. Then the COUNTIF formulas in M25:V47 check for duplicates.

The COUNTIF formula in L26 then determines if there are any duplicates by checking to see if there are any values greater than 1 in the M25:V47 table.

It is the formula in L26 that the code monitors to determine if there needs to be a re-randomization of the current week. If anything other than 0 is in L26 after the current week's pairings are checked, then there must be at least one duplicate and the week must be rerun.

As I mentioned in my previous post, this process is not as clean as it could be. The worksheet is also not as pretty as it could be. I managed to come up with something that works even if it is a bit clunky. At that point I quit and posted what I had. Once you understand how it works, you should be able to make it look nice. If not, just come on back.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question