How to check if two lists of number ranges overlap, problem with IF

[Anthony]

Hi,


I have a problem that I've battled with but haven't been able to overcome and I was hoping that someone could point me in the right direction. I have a list of ranges defined in two columns that I would like to be able to compare to a second list of ranges that are also defined in two columns. For example this is how the list of ranges that I want to query against looks like (I call the first column A and the second column B):


A_______________B

17217812_______17253252

116044149______116112949

159550790______159601159

199594765______199613428

235272325______236063911


I then have some ranges generated that I want to be able to easily check with formula to see if any of them overlap with any of the ranges in the example above. For eaxample I might generate a list that looks like this:


C____________D

235267472____235272980

232926692____233021820

201678345____201740432

181912089____182124367

235267472____235272980


At first I did a lot of searching and eventually found the great example in another thread http://chandoo.org/wp/2010/06/01/date-overlap-formulas/ So I adapted the formula to read the following:


=IF(OR($A$2:$A$6<C2,D2<$B$2:$B$6),"Do not overlap","Overlap")


And then filled it down. Here is the problem - it only works if the overlapping ranges are adjacent to each other - I think this may have something to do with the way that I've defined the list of ranges. For example above you can see that the range in C2D2 and C6D6 are actually the same and overlap with the range in A6B6, but using my formula only the range in C6D6 is marked as "Overlap" because it directly adjacent to the range with which it overlaps, and here I am stuck despite hours of fiddling around and searching for an answer I always end up back at the same problem.


Am I on the right track or should I be using a completely different approach?


Any help would be much appreciated,

Anthony

 

[Hui]

Anthony


DFo you want to check the first CD pair against all the ranges in AB or just the same row?

 

[Anthony]

I want to check each range in CD against all the ranges in AB.

 

[Anthony]

Wow, I think I made this work (sort of). I altered my formula to the following:


=IF(OR(D2<A2,B2<C2),"Don't Overlap","Overlap")&" "&IF(OR(D2<A3,B3<C2),"Don't Overlap","Overlap")&" "&IF(OR(D2<A4,B4<C2),"Don't Overlap","Overlap")...etc


Obviously this solution is extremly clunky and timeconsuming to enter into the cells for the C,D series of ranges. Can anyone think of a better solution to acieve the same result?

 

[Hui]

Anthony


I have written a small UDF which you can copy and paste into a Code Module in VBA


Assuming you data starts in Row 1

To use it enter in E1: =Overlap($A$1:$B$5,C1:D1)

and Copy down

[pre]

Function Overlap(Rng1 As Range, Rng2 As Range) As String
Application.Volatile

Dim rng1min As Double
Dim rng1max As Double
Dim rng2min As Double
Dim rng2max As Double
Dim Intersect As Integer
Dim v1 As Double
Dim v2 As Double

Intersect = 0

rng2min = Application.WorksheetFunction.Min(Rng2)
rng2max = Application.WorksheetFunction.Max(Rng2)

For i = 1 To UBound(Rng1())

v1 = Application.WorksheetFunction.Min(Rng1(i, 1))
v2 = Application.WorksheetFunction.Max(Rng1(i, 2))

If v1 <= v2 Then
rng1min = v1
rng1max = v2
Else
rng1min = v2
rng1max = v1
End If

If rng2max < rng1min Or rng2min > rng1max Then
If Intersect = 1 Then 'Doesn't Intersect
Intersect = 1
Else
Intersect = 0
End If
Else
Intersect = 1 'Does Intersect
End If

Next i

If Intersect = 1 Then
Overlap = "Overlap"
Else
Overlap = "No Overlap"
End If

End Function

[/pre]

 

[Anthony]

Wow thanks Hui, I'll give that a go and see if I can make it work - I really appreciate that!

 

[Luke M]

Smaller solution:

=IF(SUMPRODUCT(($A$2:$A$6<=D2)*($B$2:$B$6>=C2))>0,"Overlap","No Overlap")