Hi,
I have a really big table in Excel that has multiple rows where each row contains a comparison between two employees that had worked over the same time. See examples of some sample rows in the image below:
My aim is to find out which employees had the highest income during the day but I do not want employee times to overlap. So for example, in the above sample data Employee 145 is overlapping from 17:00 to 17:30 with 3 other employees and in this case, employee 147 has the highest income out of all. So I would want only one record of employee 145 with their start time changed to 17:30 and their end time unchanged at 18:00 as they were not the highest earning employee during 17:00-17:30.
In the case of employee 150, its a little bit more complicated as they have 3 overlaps each at a different time. The result I'd want is employee 150's start time unchanged but their end time should change to 15:15 as during 15:15 to 16:00, employee 152 has the highest income.
In the case of employee 160, I'd want to first truncate their entry at 12:15 as employee 161 has much higher income from 12:15-12:45 but then I'd want to insert an additional row in the table for employee 160 having a start and end time of 12:45 and 13:00 respectively as they dont have an overlap during that period and are the highest earning employee of that time.
I appreciate that there may be more scenarios to this but my goal is to find out the highest earning employees during each time of the day without any overlaps. I'm still a beginner at VBA so I was wondering if something like this could be done using VBA?
Any other approaches to the problem or help would be highly appreciated as well!
I have a really big table in Excel that has multiple rows where each row contains a comparison between two employees that had worked over the same time. See examples of some sample rows in the image below:
My aim is to find out which employees had the highest income during the day but I do not want employee times to overlap. So for example, in the above sample data Employee 145 is overlapping from 17:00 to 17:30 with 3 other employees and in this case, employee 147 has the highest income out of all. So I would want only one record of employee 145 with their start time changed to 17:30 and their end time unchanged at 18:00 as they were not the highest earning employee during 17:00-17:30.
In the case of employee 150, its a little bit more complicated as they have 3 overlaps each at a different time. The result I'd want is employee 150's start time unchanged but their end time should change to 15:15 as during 15:15 to 16:00, employee 152 has the highest income.
In the case of employee 160, I'd want to first truncate their entry at 12:15 as employee 161 has much higher income from 12:15-12:45 but then I'd want to insert an additional row in the table for employee 160 having a start and end time of 12:45 and 13:00 respectively as they dont have an overlap during that period and are the highest earning employee of that time.
I appreciate that there may be more scenarios to this but my goal is to find out the highest earning employees during each time of the day without any overlaps. I'm still a beginner at VBA so I was wondering if something like this could be done using VBA?
Any other approaches to the problem or help would be highly appreciated as well!
