Need formula to populate a schedule based on multi criteria! PLEASE!

[kara051678]

Hello, I have been researching all day and haven't found a formula populates a schedule based on the most senior persons shift and position preference. I've copied the data below to help someone understand what I am referring to. Each week the employees fill in their shift and position preferences and the supervisor goes through and makes the schedule based of this data. It's like human Sudoku! If anyone has a formula or advise on how to create a schedule that populates based of the preference data, I would be forever grateful!



Senority 1st Shift 2nd Shift 3rd Shift Position 1 Position 2 Position 3 Position 4
235 2 1 3 2 1 4 3
236 3 2 1 3 4 1 2
237 2 3 1 2 4 1 3
238 2 1 3 1 3 4 2
239 1 3 2 4 3 1 2
240 1 3 2 2 4 1 3
241 3 2 1 3 4 2 1
242 2 1 3 4 3 2 1
243 1 2 3 1 2 3 4
244 3 1 2 1 3 4 2
245 2 3 1 2 4 1 3

[zbor]

Can you write desired output (manually)?

[kara051678]

A schedule would be populated with the appropiate team members seniority number

1st Shift 2nd Shift 3rd Shift
Position 1 These fields would be
Position 2 populated with seniority
Position 3 numbers
Position 4

You can also see on the attached fileSchedule By Preference.xlsx

[zbor]

I got that.
But write in those fields seniority numbers.. Like 235, 236...

[kara051678]

Oh! I get what your asking. I have manually populated it using our current process. It is ranked by Shift preference first, then each position. If no position is left in their first SHIFT preference we move to their second shift preference then start over with POSITION preference. Schedule By Preference.xlsx

[zbor]

Sorry, I don't see the logic

[kara051678]

First I choose the most senior person,
He has given his shift preference a 1, 2 or 3 (1 being the most preferred and 3 being the least).
He has also given his position preference a 1, 2, 3 or 4 (1 being the most preferred and 4 being the least).
So for employee #235 he gets 1st shift on position 2.
The next most senior person is #236 who prefers 3rd shift, position 3.
As you go down the list you find that a person's first preference is filled already.
For example:
#237 prefers 3rd shift, position 3... But that is already filled by # 236
His next position preference is position 1, which is open so he gets that spot.

If a person’s shift preference is full, you then go to their next shift preference and start over on the position.

...you see why I want to automate this?!!!
Here is a little color... hope it helps
Schedule By Preference.xlsx

[zbor]

Here, use this.
This is an UDF macro function and you can not use it in your workbook unless you copy code there.
Or copy your data into this workbook
that must be saved as .xlsm

It works this way:

Select range where you entering function: in this case M2:P6

Then enter it as any other function:

Please Login or Register  to view this content.

In this case it would look like:

Please Login or Register  to view this content.

And then confirm it by CTRL+SHIFT+ENTER (not just enter) so curely brackets {} appear around formula.
Those brackets can not be written in formula. They must appear after confirming formula as explained.

[zbor]

As you can see I've add more data for testing.
But 0's appear because there is no enough data in A column.
You must take care to have enough data.

So if you have 4 shifts and 5 positions then you need 20 (4*5) values.
Not 12 as in this case or you get 0's.

And you need to extend range also to match those 20 values.

Please Login or Register  to view this content.