Auto-filling Groups after selection of ranked choices

[WBYF]

Hi,

I have tried a few ways at achieving this but have not had any luck.

I have attached some sample data. I am looking to see if excel can auto fill the group lists depending on the choices people have made.

The data is collected through MSForms. I have added Row A as they are given an option for each day and a summary table on the right with the max allowed for each activity.

Each person is given three choices on which activity they want to do. There is a maximum number of people in each activity. Priority is given to those who submit their choices first etc.

Is there a way to automatically allocate everyone based on their choices?

Thanks

W

[JeteMc]

Hello WBYF and Welcome to Excel Forum.
"Is there a way to automatically allocate everyone based on their choices?"
I don't believe so because the sum of the max numbers is 56 and there are 65 people.
In this instance would you allow only the first 56 persons who signed up to participate or would you take first choices until you reached the max number of each sport which would exclude some people who signed up on 5/7 with a first choice of football but include a person who signed up on 5/9 with a first choice of tennis?
I also need to ask if you are only seeking VBA based solutions, or if a formula based solution (probably employing helper columns) is acceptable?

[WBYF]

Hi JeteMc

Thank you for for reply.

It doesnt matter whether it is VBA or not - the key is that this can do the job which manually can take hours and hours - I am also keen to improve my knowledge of solving these sorts of problems.

Ideally choice would always trump time of submission but in the case of filling it would fill in order of time. So i would fill 1st choices until the max has been reached.

Sorry for the mismatch in the example in the real thing everyone should be doing something but it may not have been a choice of theirs.


Thanks for your help!

[JeteMc]

This proposal employs four helper columns per day. Note that the helper columns may be hidden for aesthetic purposes.
Note that the proposal employs sorting the date/time column from oldest to newest.
The first helper column is populated using: =IF(COUNTIFS(C$3:C3,C3)>=INDEX(AA$4:AD$4,MATCH(C3,AA$3:AD$3,0)),"",C3)
The second helper column is populated using:

=IF(F3<>"","",IF(SUM(COUNTIFS(F$3:F$67,D3),COUNTIFS(G$2:G2,D3))>=INDEX(AA$4:AD$4,MATCH(D3,AA$3:AD$3,0)),"",D3))

The third helper column is populated using:

=IF(OR(F3<>"",G3<>""),"",IF(SUM(COUNTIFS(F$3:F$67,E3),COUNTIFS(G$2:G$67,E3),COUNTIFS(H$2:H2,E3))>=INDEX(AA$4:AD$4,MATCH(E3,AA$3:AD$3,0)),"",E3))

The fourth helper column is populated using: =F3&G3&H3
Note that the fourth column (I2:I67) is given the name Monday
Note that these four columns will need to be repeated for each day.
The table is then populated using:

=IFERROR(INDEX($B$3:$B$67,AGGREGATE(15,6,(ROW($3:$67)-2)/(INDIRECT($AA$2)=AA$3),ROW(1:1))),"")

The formula that populates the table enables the names to be shown for the appropriate day (based on cell AA2) without needing a table per day.
Note that I changed the Max numbers for demonstration purposes.
Let us know if you have any questions.

[WBYF]

Hi,

This is amazing, however I am coming across some problems when i try and edit the formulae.

In reality there are 6 options and in fact this may again vary, I have tried to change the formula myself but some clumns seem to now max out not in the values entered in AA4:AF4.


The changes I have made are as follows

F3: =IF(COUNTIFS(C$3:C3,C3)>=INDEX(AA$4:AF$4,MATCH(C3,AA$3:AF$3,0)),"",C3)

G3: =IF(F3<>"","",IF(SUM(COUNTIFS(F$3:F$138,D3),COUNTIFS(G$2:G2,D3))>=INDEX(AA$4:AF$4,MATCH(D3,AA$3:AF$3,0)),"",D3))

H3: =IF(OR(F3<>"",G3<>""),"",IF(SUM(COUNTIFS(F$3:F$138,E3),COUNTIFS(G$2:G$138,E3),COUNTIFS(H$2:H2,E3))>=INDEX(AA$4:AF$4,MATCH(E3,AA$3:AF$3,0)),"",E3))

I3: No change

AA5: =IFERROR(INDEX($B$3:$B$138,AGGREGATE(15,6,(ROW($3:$138)-2)/(INDIRECT($AA$2)=AA$3),ROW(1:1))),"")

New columns to incorporate events

AE: =IFERROR(INDEX($B$3:$B$138,AGGREGATE(15,6,(ROW($3:$138)-2)/(INDIRECT($AA$2)=AE$3),ROW(1:1))),"")

AF: =IFERROR(INDEX($B$3:$B$138,AGGREGATE(15,6,(ROW($3:$138)-2)/(INDIRECT($AA$2)=AF$3),ROW(1:1))),"")

All dragged down to row 35.

Thank you for your help

[JeteMc]

...I have tried to change the formula myself but some clumns seem to now max out not in the values entered in AA4:AF4...

Looking at the formulas I can't see any problems.
Perhaps if you could upload a sample of the file that displays the issue we can come up with a solution.
Let us know if you have any questions.

[WBYF]

Hi,

Thank you for all your help with this. I have attached the file - I also notice that some are not being allocated an event despite there being space.

Once again thank you for this

Will

[JeteMc]

As for Excel's part in the issue, in the name manager > named range "Monday" > the 'Refers to:' was only referencing =Sheet1!$I$3:$I$67
Change that to read: =Sheet1!$I$3:$I$500
As for the part of the issue that isn't Excel's, the nine blanks in column I, the sum of the max participants in AA4:AF4 is 130. There are 136 people listed in column B so the max number of participants for at least some of the activities will need to be increased.
Let us know if you have any questions.