Sifting thro' data to find Double Shift

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.

Who is Participating?
Jerry PaladinoConnect With a Mentor Commented:
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.

tezza73Author Commented:
Ooops forgot file
What constitues a double shift? Is it just more than one entry per day?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Cory VandenbergSenior Risk ManagerCommented:
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,
Cory VandenbergConnect With a Mentor Senior Risk ManagerCommented:
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,
tezza73Author Commented:
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

tezza73Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.