Usinf If/Then Formulas to Optimize a Schedule Exported as an Excell File

Good afternoon!

I am an account manager for a valet company. In the past, we used a scheduling platform called "When2Work." Every manager loved it, it was intuitive and clean looking. However, our company not only wanted to cut costs by not paying for it, but also more closely track our payroll spending by using a scheduling function within our payroll application, Unifocus LMS. Well, it is great for forecasting payroll week-by-week...but not much else!

So, if you are looking at the attached file, the sheet titled "StyleSheet" is what has been exported from Unifocus LMS. The sheet "Clean Schedule" is what I want to populate.

Let's start with one example of what I want to do. B7 on "Clean Schedule" is within the row for the AM Shuttle driver, only one of which works each day. If you look on "Style Sheet," you can find his shift in each column---"4a - 12p." I'd like to construct an If/Then formula that locates that scheduled time range, and then copies the employee's name from column C on "StyleSheet" and places it within B7 on "Clean Schedule," along with the time he is working -- 4a-12p.

Similarly, I would do this with the other job classes. The problem with the time range is that there are several GSAs working each day from 7a - 3p, several working from 3p - 11p, and GSCs working at the same time (they are located in a different box on "StyleSheet," but I am not sure how to define this within a If/Then formula).


When I type out what I wish to accomplish, it seems extremely high level. Is this even remotely possible using If/Then statements??


Best,



Wes

I don't think you would do this using IF functions - INDEX/MATCH is probably the most suitable combination, but I can't see any reference to AM Shuttle on the StyleSheet so the example you quote doesn't make any sense to me. Can you give some more examples where the references in column A of the Clean_Schedule sheet can be found on the other sheet.

Pete

Thanks for your response! Unfortunately, I am still waiting for a job code to be created for the Shuttle position, so right now it is just defined as "hotel gsa" on the "style sheet." Therefore, the AM Shuttle can be identified as a hotel gsa shift occuring between the hours of 4am - 12pm.

Furthermore, the rest of the positions on the "clean schedule" sheet can be defined as follows...

AM GSA - Hotel gsa 7a-3pm
AM GSC - Hotel gsc 7a-3pm
PM GSA - hotel gsa. 3p-11p
PM GSC. - hotel gsc 3p-11p
PM Shuttle. - hotel. Gsa 3p-12am
ON GSA. - hotel gsa 11pm-7am

Ideally, seperate job codes will be created for each of those positions i have defined, but unfortunately that is what I am working with at the moment.

Thanks!

In the banner on the Clean sheet, (A1:I3) I see this:

Week of 4/15/3019

and the dates below it have values starting from that. What kind of date is this?

Pete

We make a schedule week by week, Monday through Sunday. That was just a random date set as a place holder. Normally, the dates on the "clean sheet" would match those on the "style sheet."

I was too tired to post back to you last night, although I did come up with a solution (see attached file).

I put the table that you gave me in Post #3 into columns K L and M of the Clean Schedule sheet. I also changed the banner heading to pick up the date automatically from cell D7 of the StyleSheet, with a similar formula in the merged cell B4:B6, so that the sheet would be more general purpose.

I then tried this formula in the merged cell B7:B9 (it's not a good idea to merge cells):

=IFERROR(INDEX(StyleSheet!$C$11:$C$49,MATCH(VLOOKUP($A7,$K:$M,3,0)&"*"&VLOOKUP($A7,$K:$M,2,0)&"*",StyleSheet!D$11:D$49,0)),"")

This was then copied across and down, and it seemed to give the results you were expecting. However, I then noticed that you have duplicates of "AM GSA" and "PM GSA" (whatever they mean) in column A, so that meant a different approach using an array* formula. As you can't use an array* formula in merged cells, I had to un-merge all the cells below row 6 and then remove the 2 redundant rows between each set. I've adjusted the row height so that it looks the same as before. Then I put this array* formula in cell B7:

=IFERROR(INDEX(StyleSheet!$C$1:$C$49,SMALL(IF((ISNUMBER(SEARCH(VLOOKUP($A7,$K:$M,3,0),StyleSheet!D$11:D$49)))*(ISNUMBER(SEARCH(VLOOKUP($A7,$K:$M,2,0),StyleSheet!D$11:D$49))),ROW(StyleSheet!D$11:D$49)),COUNTIF($A$7:$A7,$A7))),"")

* NOTE that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

The formula can then be copied across and down into the other cells in your table. The attached file shows the result.

Hope this helps.

Pete

No response after 3 days - did this work for you?

Pete