Sifting thro' data to find Double Shift

Posted on 2009-12-18
Last Modified: 2012-08-13
Hi Experts

Firstly A Merry Christmas to you all, I think you do a sterling job helping us amateurs. At my age now, it probably is too late to try and grasp all that Excel can offer, but your help is very much appreciated. And that is not a sychophantic statement either !

I have yet another problem which I hope you may be able to help as I am totally stumped. I need to interrogate a huge SAP download to determine whether shift people have worked "Double shifts".

The daily shift pattern is Mornings 7.8 hours, Afternoons 8.2 hours, Nights 7.2 hours. It is a 5 Crew Shift System on a 35 day rolling calendar. The list is about 13000 rows of data and if I were to attack this manually, I would be in cuckoo land by the end. (If, indeed, I could get to the end).

The list is a download sorted in Shift, Name and Date order.
I hope that you can help.

Question by:tezza73

    Author Comment

    Ooops forgot file
    LVL 76

    Expert Comment

    What constitues a double shift? Is it just more than one entry per day?
    LVL 18

    Expert Comment

    Could you provide a little more info on what criteria constitute a "double-shift"?

    My guess would be this:
    double shift if
    Pers. No = Pers. No
    Date = Date
    Shift <> (not equal) Shift

    I'm not sure if someone could have the same value for Shift on the same day, but another field would actually have different values which would mean they worked a double-shift.

    I'll start working on what I believe the solution is, but please provide feedback.  If I don't hear back in an hour, I unfortunately will be on the road back home and not able to check back for a couple of days.

    Merry Christmas,
    LVL 18

    Assisted Solution

    After looking at your data some more, I'm more in line with Graham's thinking that you just need to check the Pers. No and Date.

    Here is an example using SUMPRODUCT and then a filter on that column.

    It runs a bit slow.  If it were my project, I'd probably create a macro to cut each person one at a time to another sheet and then filter just that person, copy the needed rows to a 3rd sheet that collated all double shifts.  Unfortunately, I don't have time today to give you that solution.

    Merry Christmas,

    Author Comment

    Hi experts
    Sorry guys, I had to go out and this first time back.

    A double shift is when a person works his own shift, say afternoons, and his relief mate phones in unbale to get to work for whatever reason, so the guy working the afternoon shift elects to work the night shift too!

    So his entry would be:
    30-August      8.2
    30-August      7.2

    Hope that clarifies

    LVL 16

    Accepted Solution

    From looking at the data it appears that to identify a double shift the Name and Date must be the same and the Hours greater than zero and the Status=blank ("").  All the other Status values appeared to be some type of leave.  Using that criteria and SUMPRODUCT, the formula
    =SUMPRODUCT(  --($C$2:$C$13000=C2),  --($D$2:$D$13000=D2),  --($E$2:$E$13000>0), --($F$2:$F$13000="")  )
    A Pivot Table is included to report the data by Month.  As WarCrimes stated the SUMPRODUCT function runs slow on this many records so I turned off Calculation until it was needed and then I did a calc manually (F9).  Calc is back to Automatic in the attached file but you can set it to manual.  It only has to calculate once for the SUMPRODUCT to update and then everything is in the Pivot report once it is refreshed.


    Author Closing Comment

    Thanks both, I have awarded shared points based on the fact that warcrimes was almost there and sewed the seed, so to speak. ProdOps was spot on, thanks both.
    Have A Merry Christmas

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now