Just checking to see if there is a solution to this scenario:

Employees are bidding on 14 shifts over 7 holidays i.e. day shift and swing shift for 7 holidays. There are 20 shifts per holiday so total shifts available are 140. Each employee selects the shift they want to work with the number 1 and down to number 14 for the shift they least want to work. Each employee will have to work 2 out of the 7 holidays and are ranked by seniority.

Spreadsheet has been set up with each employee in order by seniority. Also, there is a page for each of their bids 1-14 with column headings A-N representing the 14 different shifts.

Problem: What kind of formula or macro is necessary to start at the top, put in the first person's number 1 and 2 choices in the right columns representing each of the shifts, then go on to the second person's row and enter their number 1 and two choices. Once 10 shifts per shift i.e. 10 A's are filled then no more A choices can be used, and so on until all shifts are filled.

Impossible? I'm sure that either there is a solution to this already or it can be dissected into enough pieces and pulled together in order to make it work.

Thanks in advance for all the wizard minds out there,

Rod