Script to detect overlapping values based on two variables

[enjam]

Hi all,

I'm trying to develop a function that detects overlapping values based on two variables:

(1) shift start and end time, and
(2) start chainage and end chainage (i.e. distance from a particular location).

My initial approach was to try and make rectangles (with shift start and end times defining the length of the rectangle and the chainages defining the height), then looking for overlaps in the rectangles drawn using a VBA script. Unfortunately the shapes ended up far too small for overlaps to be detected.

Attached is what I'm trying to achieve, a macro that looks at overlaps between start and finish times, then also checks for overlaps in chainage, and creates an error message where an overlap is detected.

Any guidance would be greatly appreciated.

Thank you,
enjam

[p45cal]

In the attached there's a new sheet called Input (2) which is just a copy of your Input sheet so that if I made a mistake I'd still have your original sheet. I then made a proper Excel table of your table.
On that new sheet is a new table at cell H1 which is the same as the table on the left with 3 added columns:
1. An index column, needed because Job numbers are duplicated in the first column and referring to just job numbers could refer to two other rows, so the index column makes each row unique and easily identifiable.
2. A new Job column.
3. An index pointing to the other index column where there's an overlap.
The green table on the right needs to be refreshed if the data on the left is changed; done by a right-click on the table and choosing Refresh.

This was made harder by the duplicate job numbers and that Chainage Start was not always smaller than the Chainage End (yellow highlight).

There is no macro, just a Power Query.

[enjam]

Hi Pascal,

That's brilliant, works exactly as intended. Just need to get my head around the steps you used in PowerQuery now!

Thanks very much.