Seniority Shift Bidding

[VaatiAstora]

Okay, say we have a 4 shift schedule at my business, a, b, c, and d. Each shift has 4 slots to be filled and each shift has to have at least 2 people in it.. Each employee fills out a shift ballot, that says in what order the shifts are based on their preference, like mine is c, d, a, b. C being most desired, B being least. And seniority is also a factor, if 5 people want B shift, only the 4 that have been here the longest get it. the 5th person will get his 2nd choice and so on. How would i go about creating a spreadsheet that if I list all the employees in order of seniority in column a, and their preferred shift order in columns b-e, it will make a chart for each shift with who is on the shift, like such.

Input Table:

Morse	a	b	c	d
Godwin	c	a	b	d
Slaton	c	a	b	d
Mancuso	c	b	a	d
Brown	c	d	a	b
Jenner	d	c	a	b
Sparks	a	d	c	b
Jarvis	d	a	b	c

Output Table:

A	B	C	D
Morse	Mancuso	Godwin	Brown
Sparks	Jarvis	Slaton	Jenner

Had to scale it down to 8 people to meet website limitations. I also need the tables to work if say someone quit so we only have 7 people, I just want the shift to have a blank space in that case.

Sorry if I'm asking for too much.

 

[petertenthije]

I would include one additional table, to tell how many people should be assigned to a shift.

My formula makes the following assumptions:

• The input table you created is listed in columns A:E
• The input table includes headers (your example does not)
• The desired number of people per shift is listed in J1:K4.
• Column J has the shifts: J1 = A, J2 = B, J3 = C, J4 = D
• Column K has the amount of people per shift, adding up to the number of employees available. (e.g. 4;4;4;4 or 2;6;4;4)
• The output is calculated in column F

The person with most seniority always gets what he/she wants. Therefor in cell F2 there is a direct reference to B2. (remember, B1 and F1 are headers)

In cell F3 the following formula is placed:

=IF(COUNTIFS($F$1:$F1,B2)<VLOOKUP(B2,$J$1:$K$4,2,FALSE),B2,IF(COUNTIFS($F$1:$F1,C2)<VLOOKUP(C2,$J$1:$K$4,2,FALSE),C2,IF(COUNTIFS($F$1:$F1,D2)<VLOOKUP(D2,$J$1:$K$4,2,FALSE),D2,E2)))

Columns A:F can be added into a pivot table to create the output.

 

[VaatiAstora]

I would include one additional table, to tell how many people should be assigned to a shift.

My formula makes the following assumptions:

• The input table you created is listed in columns A:E
• The input table includes headers (your example does not)
• The desired number of people per shift is listed in J1:K4.
• Column J has the shifts: J1 = A, J2 = B, J3 = C, J4 = D
• Column K has the amount of people per shift, adding up to the number of employees available. (e.g. 4;4;4;4 or 2;6;4;4)
• The output is calculated in column F

The person with most seniority always gets what he/she wants. Therefor in cell F2 there is a direct reference to B2. (remember, B1 and F1 are headers)

In cell F3 the following formula is placed:

=IF(COUNTIFS($F$1:$F1,B2)<VLOOKUP(B2,$J$1:$K$4,2,FALSE),B2,IF(COUNTIFS($F$1:$F1,C2)<VLOOKUP(C2,$J$1:$K$4,2,FALSE),C2,IF(COUNTIFS($F$1:$F1,D2)<VLOOKUP(D2,$J$1:$K$4,2,FALSE),D2,E2)))

Columns A:F can be added into a pivot table to create the output.

2 things, How would I need to edit the Formula if i wanted to add headers to the j1:k4 table. and 2, how did I **** up, since Matt should have A Shift.

 

[VaatiAstora]

View attachment 1044
2 things, How would I need to edit the Formula if i wanted to add headers to the j1:k4 table. and 2, how did I **** up, since Matt should have A Shift.

Okay, never mind on both of those points, got both of them working perfectly, now I'm just having trouble on the pivot table, it keeps wanting to give me the count of people each shift, I need their actual names. I'm using columns as "Shift Given" and the only options for values are number functions.

 

[petertenthije]

No headers needed in J1:K4.

I made a mistake with the formula, because of the headers I added later. Please see below in red the changes.

=IF(COUNTIFS($F$1:$F2,B2)<VLOOKUP(B2,$J$1:$K$4,2,FALSE),B2,IF(COUNTIFS($F$1:$F2,C2)<VLOOKUP(C2,$J$1:$K$4,2,FALSE),C2,IF(COUNTIFS($F$1:$F2,D2)<VLOOKUP(D2,$J$1:$K$4,2,FALSE),D2,E2)))

 

[petertenthije]

Add the following formula in cel G2, and copy down:
=F2&COUNTIFS($F$1:$F2;$F2)


Add a new table for the output. For simplicity sake I copied it below the input table in cells A21:E26, but obviously you can place it anywhere you want including a seperate worksheet.
Copy the formula in cell B22 into cells B22:E26.

	column A	Column B	Column C	Column D	Column E
Row 21	Number	A	B	C	D
Row 22	1	=IFERROR(INDEX($A$2:$A$17,MATCH(B$21&$A22,$G$2:$G$17,0),1),"")
Row 23	2
Row 24	3
Row 25	4
Row 26	5
Row 27	and so on

 

[VaatiAstora]

No headers needed in J1:K4.

I made a mistake with the formula, because of the headers I added later. Please see below in red the changes.

=IF(COUNTIFS($F$1:$F2,B2)<VLOOKUP(B2,$J$1:$K$4,2,FALSE),B2,IF(COUNTIFS($F$1:$F2,C2)<VLOOKUP(C2,$J$1:$K$4,2,FALSE),C2,IF(COUNTIFS($F$1:$F2,D2)<VLOOKUP(D2,$J$1:$K$4,2,FALSE),D2,E2)))

Thank you so much!