Hi everyone! (DISCLAIMER, all employees names on attached timesheet are made-up. No sensitive data attached)
I have been given a task to look at time punch sheets and locate employees who have come into contact with a 'Primary Employee'.
I get dates to look between to see what date and time this primary employee has checked in and out and locate the other employees who have come into contact with them, and what shifts were they in contact with them.
I can manually do this on my own and it is pretty simple when you have small data sets. However, this last one was a big task for me and I need help to see if I can make the task more efficient. I have explained below what I am trying to accomplish and then below that I have explained what I do on smaller data sets. I have uploaded a file with 2 sheets.
The 1st sheet has the employees I need to investigate. The 2nd sheet has the 'Primary Employee' and the dates and times I need to investigate, but I will explain here:
Between 7/2/2020 and 7/7/2020, the primary employee 'B-Nicole Aricole' worked 7/2, 7/5 and 7/7.
This means I can eliminate any shifts that are on 7/3, 7/4, 7/6 and 7/8 (overnight shift from 7/7).
I also know that the primary employee worked:
7/2/20 between 2:20 PM and 9:50 PM so I can eliminate other employees' shifts who checked out before 2:20 PM and checked in after 9:50 PM
7/5/20 between 2:20 PM and 9:43 PM, same as above
7/7/20 between 2:17 PM and 9:43 PM, same as above
Obviously if all of an employees shifts are deleted due to no crossover, say if they only worked days that the primary employee didn't, I am able to delete that employee (column A) completely.
Seems pretty simple, and usually it is. But here are the problems I encounter:
A) All the shifts an employee has registered will be covered by a single name in a merged cell (column A) meaning I can't delete entire rows with sometimes deleting the name
B) Employees will clock in and out multiple times a day at this particular location meaning there is a much larger amount of data. Some people will have some shifts on 7/2 for example which will overlap with the primary employee but will have some that don't. I can delete the ones that don't but will still need the ones that do.
C) I need the employees in alphabetical order, and I can't do that with merged cells.
D) Even after all of that, I need to get rid of blank rows. But I can't do that when there are merged cells.
What I have done on smaller data sets is create conditional formatting to locate the dates and times I can delete and delete them manually.
Also what I have done in the past on smaller data sets is unmerge the cells and essentially double click an employees name cell which copies the name cells next to every row of an employees times. That creates 2 solutions as it A) means i can alphabetize, and B) means I can delete rows with blank data.
However, that also means once I have done that, I will still need to go back and manually merge those names together as they only want 1 name for the list of that persons time check ins (if that makes sense). So on big data sets like this one, it is not really a viable solution unless I want to spend hours manually clicking. This particular issue will be come clear once you open the file and see what I mean to one name to all time punches for the employee.
I'm not expecting a solution to all the problems, but if anyone can help me find a more efficient way at least for doing this, I would appreciate it.
Let me know if you have any questions!
Bookmarks