Formula or Pivot to figure out how many times a person "traded work shifts"
Hello,
As firefighters we work 24 hour shifts. As part of our work schedule, we are allowed to "trade time" between 2 employees. If I want to take off X # of hours, then I can have another employee come to work for me and then at some point in the future, I will work for them to "payback" the hours I took off. Part of my job is to track these "trade times" or what we refer to as contracts between the two employees. One piece of data I need to know is how many of these contracts were completed each month. I attached a example worksheet to problem with coming up with this number.
The trouble is that as firefighters we are shifted between fire stations during our shifts. So, for the person taking the time off, there is only one entry (FF Smith took of 24 hours). But, for the person who is replacing him/her they might show up in the excel sheet 3,4 or 5 times because of them moving between stations. This data is exported out of a MySQL database, how it appears I can't control.
My logic tells me if I can count the names of people that are off for the date, then I can divide by 2 and it will give my the number of "contracts" for the date. I don't care if "JOE SMITH" shows up 10 times on the same date, I just want to count JOE SMITH once if he shows up on the date. I tried a pivot, but could not manipulate it how I wanted it.
I hope I made my goal clear enough.
Thank you. Brent
Expert.xls
As firefighters we work 24 hour shifts. As part of our work schedule, we are allowed to "trade time" between 2 employees. If I want to take off X # of hours, then I can have another employee come to work for me and then at some point in the future, I will work for them to "payback" the hours I took off. Part of my job is to track these "trade times" or what we refer to as contracts between the two employees. One piece of data I need to know is how many of these contracts were completed each month. I attached a example worksheet to problem with coming up with this number.
The trouble is that as firefighters we are shifted between fire stations during our shifts. So, for the person taking the time off, there is only one entry (FF Smith took of 24 hours). But, for the person who is replacing him/her they might show up in the excel sheet 3,4 or 5 times because of them moving between stations. This data is exported out of a MySQL database, how it appears I can't control.
My logic tells me if I can count the names of people that are off for the date, then I can divide by 2 and it will give my the number of "contracts" for the date. I don't care if "JOE SMITH" shows up 10 times on the same date, I just want to count JOE SMITH once if he shows up on the date. I tried a pivot, but could not manipulate it how I wanted it.
I hope I made my goal clear enough.
Thank you. Brent
Expert.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well I suppose 7 was the result because Rodriguez worked on two different dates...so each date was counted once - should that only count once, are the dates correct?
barry
barry
ASKER
Okay, your formula does account for working multiple dates because it matches the dates. I did not see that. I added another date with the same information it increased correctly.
So, that is the perfect fit! I learned quite a bit playing with this.
Thank you,
Brent
So, that is the perfect fit! I learned quite a bit playing with this.
Thank you,
Brent
ASKER
Perfect help as always. Thank you.
ASKER
There are 6 people involved in this example. (Smith & Jones) (Martin & Rodriguez) (Morrison & Petes)
Let me look at your formula a bit closer to see if I see something.
thanks