Compare multiple "shift" times on two worksheets (COMPLEX)

[DavisDS85]

The problem: I am developing a scheduling application using excel and am absolutely beside myself on how to compare two worksheets without having an unGodly long macro recursively looping through both sheets.

The details: I do contract security. I have two worksheets. 1 is a "master" schedule. It lays out scheduled shift times for the week according to contract requirements under ideal conditions. The second sheet is the actual working schedule. Due to extra coverage outside the contract, understaffing and callouts, the master schedule is not always followed. I need to compare shifts on the actual schedule to contract requirements to make sure shifts are not skipped. The issue is that I cannot simply compare times. If the master schedule has 3 shifts (0000-0800, 0800-1600, and 1600-2359) on a given day, and the actual schedule has 2 shifts (0000-1200, 1200-2359). The shifts do not match, but in reality the shifts are in fact covered. Add to that, the fact that reading back and forth from a worksheet to vba is a nightmare on processing time and the fact that shifts won't always be in chronological sequence, every method I can think of is not efficient.

Possible Solutions:
I'm currently passing the master worksheet in as a range converted to an array and processing the array to generate a scripting.dictionary object. The use of the keys makes lookup comparison better. I've considered making a dictionary of dictionaries to break up days with shifts and shift time respectively (but this doesn't address the times not matching). I would have to recursively loop back through the dictionaries to find times. Copying the shifts to another worksheet to sort it may help, but the time to write and read it could prove to take even longer than looping unsorted. I've considered making a copy of the dictionary for compare reasons and deleting them as times are found on the actual (sort of a checkoff list) and returning what's left as holes in shifts, but still this doesn't address times not matching.

The final: what alternative ways can be done to catalog a master schedule in memory and compare it to a working schedule that is efficient even when times overlap?

DNA Tech Solutions

[walruseggman]

I need to compare shifts on the actual schedule to contract requirements to make sure shifts are not skipped.

If you could provide a specific example of this, or better yet, and example workbook w/ explanation of what you want, that might help get a solution.

Based on what you have said so far, I don't think loading the master schedule into an array or dictionary would be necessary. You could just use something like .Find and eliminate the need for looping through the master schedule. But again, to help you with the parameters for .Find I'd need specifics.

[DavisDS85]

Using .find evaluates the sheet for matching references. The times may not match. That's why I can't simply compare for matching values, I have to evaluate each shift. As soon as I can, I will upload the file. It is complex, just a heads up.

DNA Tech Solutions

[DavisDS85]

There are several sheets in this workbook and lots of code.

The two sheets in question are MASTER and CURRENT. (PREV, NEXT, and PROJECTED will function exactly as CURRENT does).

You could just use something like .Find and eliminate the need for looping through the master schedule. But again, to help you with the parameters for .Find I'd need specifics.

If you look at the vba module "FileSpecific" and the Sub SetBillCodes, rendering a dictionary is pretty quick and efficient. .Find will only find if a cell contains something. As Cited in my original problem, if the master schedule has a shift for 0800-1600 and the actual schedule has someone working from 0400-1200 and someone else working 1200-1800, then .find would not work. If I searched for 0800, 1600, 0400, 1200, or 1800 none of them match. None the less, the times overlap and the shift is covered. I must process each shift and do one of a few things.

1. Process the master schedule, sort it, and add times together (0000-0800,0800-1600, and 1600-2359 becomes 0000-2359) then break the times back down as coverage is found in the actual schedule. This would involve several recursive methods.

2. Process the master schedule into a dictionary broken down by individual hours (0000-0100, 0100-0200) for every hour coverage is needed. This would be more searchable on an hourly basis, but when I have staff show up in the middle of an hour (2230) it overlaps the hour (doing it by minute would just be rediculous.

3. Something else I haven't thought of