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.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.