Dealing with overlapping time ranges using Excel VBA

BBxcl

New Member
Joined
Sep 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Query.png


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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,130
Messages
5,835,569
Members
430,367
Latest member
glastonbury

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top