Maximum Number of Overlapping Intervals

Hi,

Sample data : "Updated Sample, post #5"
-Real data 100k+ row
-Decimal points number

Step:

1) Start count maximum number of overlapping intervals for "Row 2 Interval (C$2,D$2)" ,"=IF(MAX(D$2:D3)<=MIN(C$2:C3),1,"")" at row 3 to find out if "Row 3 Interval (C3,D3)" overlaps "Row 2 Interval(C$2,D$2)

2) If yes, continue to "Row 4 Interval (C4,D4)" so the formula become "=IF(MAX(D$2:D4)<=MIN(C$2:C4),1,"")"

3) Stop the formula when it return "" and count all "1". Sum of "1" place at Row 2, Column F.

4) Loop to next interval, start count maximum number of overlapping intervals for "Row 3 Interval (C$3,D$3)" , "=IF(MAX(D$3:D4)<=MIN(C$3:C4),1,"")"

5) If yes, continue to "Row 5 Interval (C5,D5)" so the formula become "=IF(MAX(D$3:D5)<=MIN(C$3:C5),1,"")"

6) Stop the formula when it return "" and count all "1". Sum of "1" place at Row 3, Column F.

.
.
.

Not sure I have read yours 100%, but try with below code;

PHP Code: 

Option Explicit
Sub overlap()
Dim lr&, i&, k&, c&
Dim A, B, min As Double, max As Double, res(), res2()
lr = Cells(Rows.Count, "C").End(xlUp).Row ' last used row
A = Range("C2:C" & lr).Value ' point A value
B = Range("D2:D" & lr).Value ' point B value
ReDim res(1 To UBound(A), 1 To 1): ReDim res2(1 To UBound(A), 1 To 1)
min = 1000000: c = 0
For i = 1 To UBound(A)
    If A(i, 1) < min Then min = A(i, 1)
    If B(i, 1) > max Then max = B(i, 1)
    If max <= min Then
        c = c + 1
        res(i, 1) = c ' res is counting increasingly (0 to max)
    Else
        c = 0: min = 1000000: max = 0
        res(i, 1) = c ' res is re-starting from 0
        i = i + c
    End If
Next
max = res(UBound(res), 1)
For i = UBound(A) To 1 Step -1
    res2(i, 1) = max - res(i, 1) ' res2 is counting descreasingly
    If res(i, 1) = 0 Then max = res(i - 1, 1)
Next
Range("F2:F1000000").ClearContents
Range("F2").Resize(UBound(res), 1).Value = res2 ' paste res2 to sheet
End Sub 


bebo021999,

1. At F16 should be 0 because interval at row 17(15,13) do not overlap interval at row 16(11,9)

2. The data, not necessary count decreasing after starting interval, example here (row 3) : "Updated Sample, post #5"

It might same count (number of overlap) , F6, F7 : "Updated Sample, post #5"

I think it's possible, but with Excel 2016 it's probably trickier than it would be with 365. Hang on in here!

In I12, you say "Stop row 5" - please explain why.

@AliGW
Because at row 5, =IF(MAX(D$2:D5)<=MIN(C$2:C5),1,"") return "" . Once the formula return "", no need to continue the formula in the next row.


@Akuini
Sorry for late reply, I took a time to test the code on several data to make sure it is working for all type of data,
and the result is ACHIEVABLE by your code. Thank you very much.

I have one last request but if it too much work or need alter many lines of orginal code(start a new code), then it is fine.
I just can get the result by flip the data (sort "List Order" from Largest to Smallest") and run the code.
My last request is start the count from last row to top.
But if it too much for you , then it is ok, for now I just can get the result by flip the data and run the code.

Last Row to Top : Example 2 3 - Reverse.xlsb