The approach this user has taken, weighted random selection with a cumulative probability series and XLOOKUP, makes sense for the problem, but it hits a wall when duplicates appear. Recalculating the probability distribution after every pick is exactly the right instinct, but doing it manually across 1,375 selections (55 groups times 25 rounds) is not sustainable. This is where the limits of traditional spreadsheet logic become clear: the tool was not designed for iterative, stateful operations like a draft.
What this user really needs is a process that remembers what has already been chosen and adjusts the odds accordingly. The method of using `FILTER` to remove picked names is correct in principle, but rebuilding the cumulative probabilities each time requires either a helper column that recalculates dynamically or a programming language like Python or VBA. Since the user is limited to Excel 2021 without LAMBDA or MAP, the practical options narrow. A VBA macro could handle the loop, generate the weighted selections, and output them to a clean table, no manual recalculations, no duplicate names. Alternatively, moving the logic to a Python script using pandas would be even more flexible, allowing the user to define weights, run the draft, and export results in minutes.
The deeper lesson here is one we see often: spreadsheets are powerful for static analysis, but they struggle with workflows that require repeated state changes. This user has a clear, well-defined problem and has shown the ability to break it down logically. That is the hard part. The next step is choosing the right tool for execution. Whether it is VBA, Python, or a dedicated random-sampling tool, the solution should handle the iteration automatically so the user can focus on the draft results, not the mechanics. We encourage exploring those options, the draft is achievable, and the data is ready to be transformed.