Formula Errors

How to Schedule 18 Golf Players for 17 Weeks Without Duplicates in Excel

Khadija Khan Oct 19, 2026 869 views

Question details

The user needs to create a round-robin schedule for 18 golf players across 17 weeks, ensuring every player competes against every other player exactly once without errors.

How to Schedule 18 Golf Players for 17 Weeks Without Duplicate Matches in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a sports or tournament schedule matrix where a fixed group of participants must play against each other across multiple weeks without repeating combinations.
Observed behavior: When copying existing scheduling formulas across columns, the spreadsheet returns formula errors or generates duplicate player pairings.

Before you start

Ensure you have a complete and finalized list of your 18 players typed into a single column before applying any matrix formulas or macros, as adding players later will break a 17-week round-robin structure.

Solution 1Recommended

Use a Round-Robin Matrix Using INDEX and MOD Formulas

Apply a mathematical shifting formula (the Polygon method) to systematically rotate players week by week without creating duplicates.

Generating unique combinations for 18 players requires a fixed pivot. By locking player 1 in place and rotating players 2 through 18 circularly each week, you guarantee no duplicate matches.

Using standard offset formulas can lead to reference errors when dragged. The MOD function safely loops the rotation back to the beginning.

Set up your grid

In a new worksheet, list your 18 players in column A (from A2 to A19). In row 1, type the week numbers 1 through 17 across columns B through R.

Lock the first player

Designate Player 1 as the pivot. In the first pairing slot, write a formula that always references Player 1 statically (e.g., =$A$2) for all 17 weeks.

Apply the rotation formula

For the remaining 17 players, use a combination of the INDEX and MOD functions to assign their opponents. A generic syntax looks like: =INDEX($A$3:$A$19, MOD(RowOffset + WeekNumber, 17) + 1).

Drag to fill the schedule

Carefully drag this formula down to fill the matchups for Week 1, then drag the entire column across to Week 17. Ensure your column references ($A) remain locked to prevent duplicate name errors.

Use a Round-Robin Matrix Using INDEX and MOD Formulas

Understanding the Math: 18 players require exactly 17 rounds for everyone to play each other once. This yields 9 matches per week, totaling 153 unique combinations.

Solution 2

Utilize VBA to Generate the Schedule

Because round-robin formulas can be highly complex and fragile, running a VBA macro script is often the most reliable way to generate the exact 153 unique matches.

Smart Spreadsheet Scheduling

Easily Create Tournament Schedules in WPS Spreadsheet

WPS Spreadsheet offers powerful formula calculation, advanced VBA macro support, and an extensive library of free templates to help you generate complex round-robin combinations seamlessly.

1. Open WPS Spreadsheet: Launch WPS Office, open a new Spreadsheet, and list your 18 players in column A.
2. Search for templates: Click on the 'Templates' tab and search for 'Round Robin' or 'Tournament Schedule' to bypass complex manual formulas.
3. Apply and customize: Select a 17-week template. Paste your player list into the designated data range.
4. Let the software calculate: WPS Spreadsheet will instantly process the matrix, ensuring no duplicate combinations or formula errors occur across your schedule.

100% compatible with Microsoft Excel formulas and functions (.xlsx)Supports advanced VBA macros for automated schedulingAccess to hundreds of free, ready-to-use tournament templatesLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get duplicate names when copying scheduling formulas?

Duplicate names usually occur because cell references are not properly locked using absolute references (like $A$1) before dragging the formula across columns, or because the rotation sequence lacks a modulo (MOD) function to restart the list.

What is the polygon method for round-robin scheduling?

The polygon method is a mathematical strategy where one participant is kept fixed in position, and all other participants are rotated in a circle (or polygon) by one position each round. This guarantees every player meets exactly once.

How do I handle a golf schedule if I only have 17 players?

If you have an odd number of players, you should add a 'Dummy' or 'Bye' player to make the total an even number (18). When generating the schedule, any actual player paired against the 'Dummy' gets a bye for that week.

Are VBA macros better than formulas for scheduling?

For larger schedules, yes. While formulas update live, a 17-week 18-player matrix requires complex nested formulas that can accidentally break. A VBA macro calculates the logic perfectly once and prints plain text results, eliminating ongoing formula errors.

Excel & Data Problems