|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:
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:
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!
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03