Random selection of participants and Permutations with Excel

Karpay750 Please see the attached workbook for another possible solution, which also requires Excel for MS365. The applicants are listed in a table on the "Applicants" worksheet. Two separate examples are provided on the "CountryFirst" and "CategoryFirst" worksheets.

As you might expect, the "CountryFirst" worksheet gives first priority to filling the target number of slots for each country. The target is based on the weight given to each country, where weight = [count of country] / [number of applicants] and target = [weight] * [number of slots]. It also attempts to fill the target number of slots for each category as a secondary objective, in order to achieve maximum diversity; however, this is not always obtainable, as the pool of applicants is reduced when each country meets its target number of slots, and the remaining applicants may not be enough to meet the target for each category.

The "CategoryFirst" worksheet is exactly the same, but prioritizes category first, then country second.

The formula to generate the list of randomly selected applicants uses LAMBDA recursion to iterate through the steps 35 times (number of slots). Each iteration filters the table of applicants to exclude all of the previously selected applicants, and to exclude any countries or categories that have already reach their targets. The CHOOSECOLS and RANDBETWEEN functions are then used to randomly select the next applicant from the filtered list. The process repeats until all 35 slots are filled.

The functions are volatile and will refresh their results any time a value is changed in the workbook. For example, manually inputting 35 again into cell B2 (No. of Available Slots) will generate a new random list. To keep a static copy of the results, select the entire output range, then Copy and Paste Special > Values in another worksheet or workbook.

This is a tough one to really explain in complete detail, but gets the job done. By the way, the number of possible permutations/combinations where n = 150 and r = 35 is a ridiculously high number, so it's extremely unlikely the same list will be generated more than once.