If range = text question

[danyalarab]

Hello,
Long time reader, first time poster.


I am creating a sports selection sheet for my school and students have to make 1st, 2nd and 3rd selections of sport on an online form.

eg - 1. Baseball, 2. Tennis, 3. swimming.

They submit this to my online form and I get a nice excel spreadsheet listing their choices. My problem is that I need to sort their selections based on first in best dressed and then start to cut off selections as the class becomes full.

So, here is my question - is there an 'if' formula that I could write that would say 'if 'selection 1=baseball ... and baseball has already received 25 selections,somehow move on to the second selection... eg someone further down the list that missed out on a quota of 25 places, then their selection 2, Tennis would be selected.

I hope that makes sense, please let me know if you need further clarification.

Thanks,

Dany

[etaf]

can we see an example spreadsheet and how setout
how do you assign the 25 - is it based on date/time received

[danyalarab]

No Problem Wayne,
I will post the spreadsheet now. It is very basic as I'm still trying to work out where to put fields etc but yes it would be based on time received. Not sure how to assign the 25 places yet, I suppose it would need to work down a column...

Thanks,

Dany
New Form 1_25_14.xls

[etaf]

well, this is not the greatest formula and may not work

=IF(COUNTIF($T$1:T1,E2)<=2,E2,IF(COUNTIF($T$1:T1,F2)<=2,F2,IF(COUNTIF($T$1:T1,G2)<=2,G2,"to many")))
and it will assign one more that the criteria - IE allow 3 items

the spreadsheet will need to be sorted into ascending date/time order, then the oldest is first

it may not work completely - but appears to work with your data
the issue of course will be if you have different volumes allowed for different courses , that's going to get a little complicated - probably can use a lookup table for each course to get the allowed volume
i'm sure some on one more knowledgeable will help with a better equation

will different courses have different volumes allowed?

look at column T and you will see in last entry - 3rd selection made
I did not sort by date at all - but that would give you the priority