# Autopopulate data

Copper Contributor

# Autopopulate data

Hi

I am trying to build a spreadsheet for a football tipping competition. (Australian NRL)

I need help to auto populate the teams into the spreadsheet.

there are 48 participants (Rows) & 27 rounds of the competition (Columns)

each round each participant is given a team (all selected before the start of the season).

The points that team scores in that round is awarded to the participants, the person with the most points at the end of the season is the winner.

The tricky part is that there are an odd number of teams in the competition, so each week 1 team gets a bye, so that team is not given to a participant (No points), i have 27 tables with all team that are playing.

I would like to randomly (but equally) distribute the teams to each round (on average there are 16 teams playing so each team should appear 3 times per round, (Column)

There are a couple of rounds that less teams playing (Minimum 10).

I would also like for each participant not to get the same team more than twice across the 27 rounds.

Is this possible??

How can i do this??

Thanks

Bill

2 Replies

# Re: Autopopulate data

it's possible to create a spreadsheet to automate this process using Excel.

Here is a general approach you can take:

1. Create a Teams Table: First, create a table that lists all the teams participating in the competition. This table should have one column for the team names.
2. Create a Rounds Table: Create another table with 27 columns (one for each round) and 48 rows (one for each participant). This will be your main table where you'll auto-populate the teams for each participant in each round.
3. Randomly Assign Teams to Rounds: Write a VBA macro to randomly assign teams to each round while ensuring that each team appears approximately three times per round on average. You can use Excel's built-in functions to generate random numbers and distribute the teams accordingly.
4. Avoid Duplicate Teams: Write additional logic in your VBA macro to ensure that each participant does not receive the same team more than twice across the 27 rounds. You can keep track of the teams assigned to each participant and check for duplicates before assigning a new team.
5. Handling Bye Rounds: Since there's an odd number of teams and one team gets a bye each round, you'll need to account for this in your assignment logic. You can randomly select one team to have a bye for each round and exclude them from the assignment process.
6. Test and Refine: Test your macro thoroughly to ensure that it correctly assigns teams to each participant in each round according to your requirements. Make any necessary adjustments or refinements to the logic as needed.

While this process may require some advanced Excel skills and knowledge of VBA programming, it's definitely achievable with some effort and experimentation. If you're not comfortable with VBA, you may consider seeking assistance from someone with VBA expertise to help you implement the automation.

If you like to achieve this without using VBA by utilizing Excel's built-in functions and features creatively. Here is a general approach you can take:

1. Create a Teams Table: As mentioned before, create a table listing all the teams participating in the competition.
2. Create a Rounds Table: Similarly, create a table with 27 columns (one for each round) and 48 rows (one for each participant).
3. Randomly Assign Teams to Rounds: You can use Excel's functions like RAND or RANDBETWEEN to generate random numbers. Then, use formulas like INDEX and MATCH to look up teams from the Teams Table and assign them to each participant in each round. You'll need to ensure that each team appears approximately three times per round on average.
4. Avoid Duplicate Teams: To prevent participants from receiving the same team more than twice across the 27 rounds, you can use conditional formatting or additional helper columns to track and manage the assignment of teams. You may also need to manually adjust assignments if duplicates occur.
5. Handling Bye Rounds: For rounds where one team gets a bye, you can manually designate the bye team for each round and exclude them from the random assignment process.
6. Test and Refine: Test your formulas and assignments thoroughly to ensure they meet your requirements. You may need to refine your formulas or make manual adjustments as necessary.

While this method may require more manual intervention compared to using VBA, it can still achieve the desired result within Excel's capabilities. However, it's essential to carefully design and test your formulas to ensure accuracy and reliability.The text was created with the help of AI.

If all these steps don’t help, here is a link with information on how you can quickly find a solution that is most suitable for you.

Welcome to your Excel discussion space!

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

# Re: Autopopulate data

@NikolinoDE Thanks for your support, it’s a bit for me to get my head around but I will give it a go.

thanks

Bill