Shift Bid Schedule Question

[HRWizard]

Good morning, I have a spreadsheet that has the name of the employee in cell A1 and all of the shifts that can be bid on from B1 through AL1.

The employees in cell A2 through cell A76 are ranked by seniority.

Each employee can choose a minimum of 3 shifts up to 5 shifts to bid on for the coming year.

Some of the shifts have more than one available slot. For example Shift 1 has 4 openings.

Is there a formula that would come back with their choice by seniority ranking?

Thank you so much -- I'm by no means an expert at Excel but I'd be happy to help with any HR questions.

 

[ndsutherland]

Can you provide a sample with expected results?

 

[HRWizard]

Can you provide a sample with expected results?

Hi,

Yes, thank you so much for replying!

It would look like the following table.

Shift 1 has 2 openings, Shift 2 has 1 opening, Shift 3 has 1 opening and Shift 4 has 4 openings.

John has the most seniority so he would get 1st choice. His first choice is Shift 1 so he is awarded Shift 1. John is second in seniority so he would get 2nd choice. Since there are 2 openings in Shift 1 he also would be awarded Shift 1. Richard's 1st choice is Shift 1 but both of the openings have been given to John and Jeremy so Richard would get his 2nd choice which is Shift 2. Mary's 1st choice is Shift 4 -- nobody else has Shift 4 so she gets it. Ann's first choice is Shift 4 and there are 4 openings so she gets Shift 4 too.

Since there are only 37 shifts and they only have to pick up to 5 -- there will be cells with no choices made in them - such as in Shift 6 below.

There are 37 shifts listed from Cell B1 through AL1 and employees listed from A3 through A77.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Number of Openings[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First Name[/TD]
[TD]Shift 1[/TD]
[TD]Shift 2[/TD]
[TD]Shift 3[/TD]
[TD]Shift 4[/TD]
[TD]Shift 5[/TD]
[TD]Shift
6[/TD]
[TD]Awarded Shift[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]Shift 1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jeremy[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]Shift 1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Richard[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]Shift 2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mary[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]Shift 4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ann[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]Shift 4[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to automatically calculate their Awarded Shift in H3 . . . by seniority and number of openings available during each shift?

I hope this makes sense!

Thanks again!

 

[ndsutherland]

Are you opposed to a UDF (User Defined Function)?

You would have paste some code into a module and save as Macro Enabled workbook.

 

[ndsutherland]

Nevermind, the formula solution isn't terribly complicated (I'm just tired)

Shift Sample

Note, this is an array formula so you must confirm with CTRL+SHIFT+ENTER (not just ENTER), this will put the curly braces around it.
Also, this won't assign anybody to shift 6 as that column contains all 0's. Let me know if that is a problem.

 

[HRWizard]

You are my savior! Thank you so much for your help! I would have been doing this manually for the rest of my life!

Thank you! Thank you! Thank you!

 

[vidguru]

I know this is an old thread, but hoping the solution is still out there, i have the exact same issue The link ndsutherland provided no longer has the file. Can it be reposted, or can someone help?

 

[ndsutherland]

I apologize for not posting the answer, I know better.


[TABLE="width: 631"]
<colgroup><col><col span="6"><col></colgroup><tbody>[TR]
[TD]
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Number of Openings[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First Name[/TD]
[TD]Shift 1[/TD]
[TD]Shift 2[/TD]
[TD]Shift 3[/TD]
[TD]Shift 4[/TD]
[TD]Shift 5[/TD]
[TD]Shift
6[/TD]
[TD]Awarded Shift[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]{=IFERROR(INDEX($B$2:$G$2,,MATCH(MIN(9^9,IF($B$1:$G$1>COUNTIF($H$2:$H2,$B$2:$G$2),IF(B3:G3>=1,$B3:$G3))),$B3:$G3,0)),"")}[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jeremy[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]{=IFERROR(INDEX($B$2:$G$2,,MATCH(MIN(9^9,IF($B$1:$G$1>COUNTIF($H$2:$H3,$B$2:$G$2),IF(B4:G4>=1,$B4:$G4))),$B4:$G4,0)),"")}[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Richard[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]Shift 2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mary[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]Shift 4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ann[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]Shift 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<body id="************" style="position: absolute; top: 38.9063px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Number of Openings[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First Name[/TD]
[TD]Shift 1[/TD]
[TD]Shift 2[/TD]
[TD]Shift 3[/TD]
[TD]Shift 4[/TD]
[TD]Shift 5[/TD]
[TD]Shift
6[/TD]
[TD]Awarded Shift[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]Shift 1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jeremy[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]Shift 1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Richard[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]Shift 2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mary[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]Shift 4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ann[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]Shift 4[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[richtangon]

Hi, I have the same issue

I apologize for not posting the answer, I know better.

A B C D E F G H 1 Number of Openings 2 1 1 4 1 1 2 First Name Shift 1 Shift 2 Shift 3 Shift 4 Shift 5 Shift 6 Awarded Shift 3 John 1 2 3 4 5 0 {=IFERROR(INDEX($B$2:$G$2,,MATCH(MIN(9^9,IF($B$1:$G$1>COUNTIF($H$2:$H2,$B$2:$G$2),IF(B3:G3>=1,$B3:$G3))),$B3:$G3,0)),"")} 4 Jeremy 1 3 5 2 4 0 {=IFERROR(INDEX($B$2:$G$2,,MATCH(MIN(9^9,IF($B$1:$G$1>COUNTIF($H$2:$H3,$B$2:$G$2),IF(B4:G4>=1,$B4:$G4))),$B4:$G4,0)),"")} 5 Richard 1 2 5 3 4 0 Shift 2 6 Mary 4 3 2 1 5 0 Shift 4 7 Ann 0 4 3 1 2 0 Shift 4 <tbody> </tbody>

<colgroup><col><col span="6"><col></colgroup><tbody>
</tbody>

<body id="************" style="position: absolute; top: 38.9063px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

	A	B	C	D	E	F	G	H
1	Number of Openings	2	1	1	4	1	1
2	First Name	Shift 1	Shift 2	Shift 3	Shift 4	Shift 5	Shift 6	Awarded Shift
3	John	1	2	3	4	5	0	Shift 1
4	Jeremy	1	3	5	2	4	0	Shift 1
5	Richard	1	2	5	3	4	0	Shift 2
6	Mary	4	3	2	1	5	0	Shift 4
7	Ann	0	4	3	1	2	0	Shift 4

<tbody>
</tbody>
</body>

 

[richtangon]

Hi, I have the same issue above with the shift bid, but somehow copied the table above and the formula but cant get it to work. can someone still help regarding this or attach a file for better reference. thank you so much