Shift Bid Schedule Question

H

HRWizard

New Member
Joined
Aug 1, 2015
Messages
3
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
ndsutherland said:
Can you provide a sample with expected results?
Click to expand...

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.

ABCDEFGH
1Number of Openings211411
2First NameShift 1Shift 2Shift 3Shift 4Shift 5Shift
6		Awarded Shift
3John123450Shift 1
4Jeremy135240Shift 1
5Richard125340Shift 2
6Mary432150Shift 4
7Ann043120Shift 4

<tbody>
</tbody>

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!
 
Upvote 0
Are you opposed to a UDF (User Defined Function)?

You would have paste some code into a module and save as Macro Enabled workbook.
 
Upvote 0
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.
 
Upvote 0
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!:biggrin::biggrin::biggrin::biggrin:
 
Upvote 0
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?
 
Upvote 0
I apologize for not posting the answer, I know better.



ABCDEFGH
1Number of Openings211411
2First NameShift 1Shift 2Shift 3Shift 4Shift 5Shift
6		Awarded Shift
3John123450{=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)),"")}
4Jeremy135240{=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)),"")}
5Richard125340Shift 2
6Mary432150Shift 4
7Ann043120Shift 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;">
ABCDEFGH
1Number of Openings211411
2First NameShift 1Shift 2Shift 3Shift 4Shift 5Shift
6		Awarded Shift
3John123450Shift 1
4Jeremy135240Shift 1
5Richard125340Shift 2
6Mary432150Shift 4
7Ann043120Shift 4

<tbody>
</tbody>
</body>
 
Upvote 0
Hi, I have the same issue
ndsutherland said:
I apologize for not posting the answer, I know better.



ABCDEFGH
1Number of Openings211411
2First NameShift 1Shift 2Shift 3Shift 4Shift 5Shift
6		Awarded Shift
3John123450{=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)),"")}
4Jeremy135240{=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)),"")}
5Richard125340Shift 2
6Mary432150Shift 4
7Ann043120Shift 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;">
ABCDEFGH
1Number of Openings211411
2First NameShift 1Shift 2Shift 3Shift 4Shift 5Shift
6		Awarded Shift
3John123450Shift 1
4Jeremy135240Shift 1
5Richard125340Shift 2
6Mary432150Shift 4
7Ann043120Shift 4

<tbody>
</tbody>
</body>
Click to expand...
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
complex shift bid seeking help
Replies
1
Views
603
hajiali
H
J
  • Question
Shifting multiple cells left/right and ignoring certain columns?
Replies
2
Views
132
joshpep96
J
M
  • Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
0
Views
52
mellojeff
M
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
448
AndyEd
A
T
  • Question
Using Excel to create a schedule table from given data table.
Replies
1
Views
622
Herakles
H

Forum statistics

Threads
1,215,417
Messages
6,124,787
Members
449,188
Latest member
Hoffk036

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top