[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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.

Regards
tezza73
0
tezza73
Asked:
tezza73
2 Solutions
 
tezza73Author Commented:
Ooops forgot file
People.XLS
0
 
GrahamSkanCommented:
What constitues a double shift? Is it just more than one entry per day?
0
 
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,
WC
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Cory VandenbergSenior 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,
WC
DoubleShift.xls
0
 
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
tezza73

0
 
Jerry PaladinoCommented:
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.
Jerry
 

People.xls
0
 
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
tezza73
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now