Should/Can I make a macro for this scenario?

[Cheryl Smith]

Scenario:
I have 10 employees and 5 holidays for which I must staff 5 employees. To determine which 5, I first need to see which of the 10 employees volunteered to work. Next, once I have the volunteers, I must determine who is least senior of the 10, they will be mandatoried. So, volunteers then least senior will make up my 5 employees. Oh and the "kicker"<<think of it as the hot sauce>>, the least senior employee(s) who works a holiday cannot work the next holiday (unless they volunteer). (Spicy, right)
I was thinking I could push a button and have a macro perform the determination for the 5 employees each holiday.
Im having trouble with the logic.
Ex parameters:
Emp#: 1-10
Emp Name: John, jake <<whoever
Emp Senior Date: 1/2/1980-12/16/2005 ( pick arbitrary dates for each employee in this range)
Volunteer: Y or N
If less senior then employee is mandatoried.

My thinking (logically, of course):
Take Emp#1, did they volunteer to work(Y or N)
If Y then schedule,
If N then is their senior date <= 12/16/2005 and > 1/2/1980, if Y then schedule,
If N then no schedule
This is performed on all 10 employees. There should be 9 names listed for scheduling. Right??
Ok, so if this is correct, how can I rotate the least senior employees who were mandated to work the previous holiday?
Ex: Emp #4 was mandated to work MLK, they may not have to work PD.

Is this wayyyyy too ambitious, especially for a macro?

 

[NARAYANK991]

Hi Cheryl ,

Can you upload a sample workbook ?

Since you talk of the previous holiday , you need to have one column which will be just data , giving the status of all the employees ; the macro can then use that data as the reference , and put in the roster for the subsequent holidays.

Narayan

 

[Cheryl Smith]

Here is the test data.
EMP# LASTN FIRSTN SENIORITY HOLIDAY WORKED VOLUNTEER MANDATE
1 SMITH JASON 1/2/1980 MLK Y Y
1 SMITH JASON 1/2/1980 PD
1 SMITH JASON 1/2/1980 CD
1 SMITH JASON 1/2/1980 VD
2 JOHNSON WILMA 3/8/1984 MLK Y Y
2 JOHNSON WILMA 3/8/1984 PD Y Y
2 JOHNSON WILMA 3/8/1984 CD Y Y
2 JOHNSON WILMA 3/8/1984 VD Y Y
3 ALBERT FRED 11/24/1990 MLK
3 ALBERT FRED 11/24/1990 PD
3 ALBERT FRED 11/24/1990 CD
3 ALBERT FRED 11/24/1990 VD Y Y
4 WILSON JENNIFER 5/3/2010 MLK
4 WILSON JENNIFER 5/3/2010 PD Y Y
4 WILSON JENNIFER 5/3/2010 CD
4 WILSON JENNIFER 5/3/2010 VD Y Y
5 BILLS TAMMY 4/1/1988 MLK
5 BILLS TAMMY 4/1/1988 PD
5 BILLS TAMMY 4/1/1988 CD Y Y
5 BILLS TAMMY 4/1/1988 VD Y Y
6 CARTER JIMMY 11/21/2001 MLK Y Y
6 CARTER JIMMY 11/21/2001 PD Y Y
6 CARTER JIMMY 11/21/2001 CD Y Y
6 CARTER JIMMY 11/21/2001 VD Y Y
7 MENDEL NELSON 1/13/1984 MLK
7 MENDEL NELSON 1/13/1984 PD
7 MENDEL NELSON 1/13/1984 CD
7 MENDEL NELSON 1/13/1984 VD
8 HOLLIS WENDEL 6/3/1981 MLK
8 HOLLIS WENDEL 6/3/1981 PD
8 HOLLIS WENDEL 6/3/1981 CD
8 HOLLIS WENDEL 6/3/1981 VD
9 BORNE JASON 5/12/1993 MLK Y Y
9 BORNE JASON 5/12/1993 PD Y Y
9 BORNE JASON 5/12/1993 CD Y Y
9 BORNE JASON 5/12/1993 VD Y Y
10 BOND JULIAN 12/8/2005 MLK Y Y
10 BOND JULIAN 12/8/2005 PD
10 BOND JULIAN 12/8/2005 CD
10 BOND JULIAN 12/8/2005 VD Y Y
MLK- MARTIN LUTHER KING JR DAY
PD - PRESIDENTS DAY
VD- VETERANS DAY
CD - COLUMBUS DAY

 

[Debraj]

Hi Cheryl

Consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary.

the new forum gives members something they have been asking for, the ability to upload files. If you look to the bottom of the box in which you post your question you will see the button to upload a file from your computer to Chandoo ; this is going to save members a lot of time and trouble.

Thank You..

 

[NARAYANK991]

Hi Cheryl ,

Unfortunately , not all of us are Americans !

Are the holidays listed in calendar order ? If not , can you list them in order ?

Also , your post mentions 5 holidays , but you have listed only 4.

Narayan