Initial population of an ARRAY in Excel

[DonEB]

I currently have a spreadsheet that is filled with functions and. using macros. loops through thousands of potential combinations searching for the best possible solution. This is all being done to try and match up tennis players who have competed the least amount of times against one another... and, to top that off... they rotate partners after each set. I have discovered that when you only have 5 players to examine, this is fairly simple process. There are only so many combination of players to deal with. When you have 12 or more players AND more than one tennis court available to play on, it becomes much more complicated.

My spreadsheet currently utilizes the =combinations(A8:A28,4) function. Where A8:A28 contains my candidates for playing (signified by a number available to play such as 1, 2, 3, etc.) and 4 being the number of people playing together.

The spreadsheet seems works fine for groups of 8 or less (i.e., two courts). But it takes more than 20 seconds to complete because with 8 people there are a total of 1820 combinations. Once I get to 12 or more (i.e., 3 or more courts)... we begin to get into minutes... not seconds.

I was trying to figure out a way to reduce the time it takes the program to loop through all of this and the only possible solution I came up with was to try and use arrays in VBA. It is my understanding that using arrays would not require spreadsheet functions to constantly calculate every time I force it to loop through the potential "combination" of 4 candidates in hopes of finding the best match. This is because it would not be writing to the spread sheet each and every time I loop through another potential set of candidates. Is that correct?

If so, here is what I will know.
1. I will know the size of the array based on the number of players that will be available each week and the number of courts available to play on.

My question is... is it possible to populate an array using the =combinations(A8:A28,4) function? And if so, might someone have a suggestion how to accomplish that?

This is just my first step but finding out if using arrays this way is possible and more efficient that my current approach. If so, then I would be encouraged to do more research to try and figure out how to make this work.

I appreciate any help or direction you can provide.

Thank you,
Don

 

[spencer_time]

Okay, I'm not sure I know enough to help, but before I can even try I need to better understand what you need.

I understand the following:
In Column A you have all the players listed (I'm guessing one per row)
You need combinations of 4 players (would you call this a match? or a team?)
There are multiple courts to take into account (what is important about the courts? is there a different set of combinations for each court? Are they just for logistical support {putting each consecutive match in the next open court regardless of what combination of players it is}{or are they completely separate, like in different states, requiring a different set of players and different set of combinations for each court?})

Here are a few questions:
How are you checking who has played against who the least number of times?
When they rotate partners do the matchups of players stay the same or do you need a completely new combination of players?

It might be helpful if you can post a small desensitized sample list of john smith and jane doe type information that has the format you're working with and the kind of matchup you would expect from said data.

To answer your question about the array. It most certainly can be done, although depending on what kind of data that function outputs it might be easy or hard. I recently had an issue trying to make a 3 dimensional array and ended up making a set of a 1D array populated with 2D arrays instead thanks to some input from people from the forums..Just had to think out of the box a little.

 

[DonEB]

Thank you for your interest in this.

To add value to your assumptions and answer your questions:

1. In Column A you have all the players listed (I'm guessing one per row)
   • YES... and there is only 1 value per row. The players are represented by a number which can range from 1 to 30 (30 potential different players). Before I get to this point, I have already whittled it down the number of players to either sets of 4, 8, 12, 16 or 20 players. With 4 players being assigned to each court, that results in a range of 1 to 5 courts being used depending on how many people are available.
2. You need combinations of 4 players (would you call this a match? or a team?)
   • A "match". These four individuals would be assigned to play on a single court.
3. There are multiple courts to take into account
   1. what is important about the courts?
      • The different courts only represent a different set of players being put together to play.
   2. is there a different set of combinations for each court?
      • YES... If you only have 8 players, that represents 2 courts. However, the BEST combination of players are those who have played against each other the fewest number of times.
   3. Are they just for logistical support {putting each consecutive match in the next open court regardless of what combination of players it is}{or are they completely separate, like in different states, requiring a different set of players and different set of combinations for each court?})
      • Not exactly sure what you mean by this but this effort becomes more complicated when this program gets to 12 or more available players because (in this specific example) the program has to determine the best combination of player for each of the three courts before it settles on a final combination of players. So, as it loops through the various combinations of for "court 1" the program has to consider removing those potential players selected from the combination of players available for "court 2" and so on.
4. How are you checking who has played against who the least number of times?
   • Within my worksheets, I have a chart which summarizes who has been assigned to play against who. This is accomplished once the final combination has been determined and written to the "tennis schedule". The "tennis schedule" is prepared quarterly showing all participants, the utilization of 1 to 5 courts each week, and generally 13 weeks of play.
5. When they rotate partners do the matchups of players stay the same or do you need a completely new combination of players?
   • The same four players stay on the same court. Players play 3 sets during the court of 2 hours. By rotating partners after each set, everyone on the court plays with all players assigned to the court. No one is stuck with the bad player, no one gets lucky and plays with the best player. It's just about fairness of play.

I wanted to provide you a couple excerpts from my spreadsheet showing the chart where I grab my player numbers from AND the "schedule" which is the final product. However, I couldn't figure out how to insert an image. Is there a simple way to insert an image or a .png file? I found an Icon for "insert image" but it wants me to refer to a URL and that I do not have.


I hope this provides enough for you to possibly provide some additional guidance.

Again, thanks for your interest in this.

Don