Forum Discussion
Random selection of participants and Permutations with Excel
Dear all,
It is the first time I will try something so complex in Excel. I hope I will be clear and use the right terms to explain it.
I am organising a workshop. I have the list of candidates with their professional category and country. I have more than 150 applications for 35 places. I want to write a script/code and ask Excel to generate a selection of 35 candidates with maximum diversity of country and maximum diversity of professional category. I want to generate random permutations to see different distributions of profiles generated by the code. Then I will compare it to human selection. Is it possible with Excel??? Thank you a lot for your help.
***Ideally I would like to add age and gender to the diversity criteria but for now, I keep calm.
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.
There are no doubt multiple ways this can be accomplished. See the attached workbook for one technique. The workbook requires Excel version 2021 or later (because it uses the LET function and the SEQUENCE function).
Row 2 of the RandomSubset worksheet summarizes the diversity. (I included two custom number formats to generate the words that are displayed with the counts.) Copy the last row of this sheet down to add places, or delete it to remove places.Edit: Corrected the random selection formula, as I had added 1 (to skip past the Candidates column headers), but in the wrong place. The original formula would sometimes generate #REF errors.
- Oh wow thank you for the example created. I will try this this weekend and come back here to share my results! amazing!
- ifpossible,share some dumy.data and your expected result.
so that I can visualize what you need.
