Hi,
I have a project which requires me to allocate people to their choices. However, each choice has different limits. I do not have any background in using VBA hence I have almost nothing to start with.
Each student has 3 choices (A, B and C) which they can rank as first, second and third. I have a total of 20 people at hand. However, there's a limit of 6 people for Choice A, 5 people for Choice B and 5 people for Choice C. That leaves 4 people who do not get their choices at the end. I have split Choice A, Choice B, Choice C and No Choice into 4 different sheets.
I would like to ask if anyone knows how to create a VBA code such that everyone's choices are sorted in the table in List of Students (Sheet 1), then they are allocated to their first choices as much as possible.
The case should go like this: If a person has Choice A as 1st choice, Choice B as 2nd choice, Choice C as 3rd choice: Check if Choice A still has vacancy, if vacancy>0, person is inserted into Choice A sheet. If vacancy=0, check Choice B sheet instead. In choice B sheet, if vacancy >0, person is inserted into Choice B sheet. And so on.
I actually have a list of more than a few hundred people in my project, with a bigger limit for each choice. So I have simplified it to this example instead. Here's my simplified excel sheet: https://www.dropbox.com/s/rgje2hs0kqehe5s/Allocation%20of%20students%20to%20choices.xlsx?dl=0
Appreciate all help! Thank you in advance!
I have a project which requires me to allocate people to their choices. However, each choice has different limits. I do not have any background in using VBA hence I have almost nothing to start with.
Each student has 3 choices (A, B and C) which they can rank as first, second and third. I have a total of 20 people at hand. However, there's a limit of 6 people for Choice A, 5 people for Choice B and 5 people for Choice C. That leaves 4 people who do not get their choices at the end. I have split Choice A, Choice B, Choice C and No Choice into 4 different sheets.
I would like to ask if anyone knows how to create a VBA code such that everyone's choices are sorted in the table in List of Students (Sheet 1), then they are allocated to their first choices as much as possible.
The case should go like this: If a person has Choice A as 1st choice, Choice B as 2nd choice, Choice C as 3rd choice: Check if Choice A still has vacancy, if vacancy>0, person is inserted into Choice A sheet. If vacancy=0, check Choice B sheet instead. In choice B sheet, if vacancy >0, person is inserted into Choice B sheet. And so on.
I actually have a list of more than a few hundred people in my project, with a bigger limit for each choice. So I have simplified it to this example instead. Here's my simplified excel sheet: https://www.dropbox.com/s/rgje2hs0kqehe5s/Allocation%20of%20students%20to%20choices.xlsx?dl=0
Appreciate all help! Thank you in advance!