[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Excel VBA Algorithm: Determine Number of Objects that Overlap Given a Date Timeframe

Posted on 2011-10-24
Medium Priority
433 Views
Hey,

I have been trying to come up with an algorithm to determine how many objects will overlap / happen at the same time, given a date timeframe.  Each object has a lifetime of 10 days.  You are given a certain number of objects, and the dates that these objects start.  My goal of this algorithm is to determine how many objects could be happening at the same time.

For example, say you are given 6 objects and their start dates:
Object 1: 9/25/11
Object 2: 9/13/11
Object 3: 10/3/11
Object 4: 10/8/11
Object 5: 10/21/11
Object 6: 10/10/11

Here is a visual of these objects to help show the idea of what I mean by object "overlap":

So... visually, it is pretty easy to see that the maximum number of objects that "overlap" is 3 (Objects 3, 4, and 6).  There is also an overlap of 2 at the beginning of October with objects 1 and 3.  So my question is: How can I find the maximum overlap of X objects, given the start dates for each object?  Any help would be greatly appreciated!

Regards,
Rob
0
Question by:RobStl
• 3
• 2

LVL 19

Accepted Solution

Arno Koster earned 2000 total points
ID: 37018470
can you try this :
Sub test()

'Object 1: 9/25/11
'Object 2: 9/13/11
'Object 3: 10/3/11
'Object 4: 10/8/11
'Object 5: 10/21/11
'Object 6: 10/10/11

objects = Array("9/25/11", "9/13/11", "10/3/11", "10/8/11", "10/21/11", "10/10/11")
Duration = 10
max_concurrent_items = 0

For check_date = CDate("9/1/11") To CDate("12/1/11")
concurrent_items = 0
For Each Item In objects
If check_date >= CDate(Item) And check_date < CDate(Item) + 10 Then concurrent_items = concurrent_items + 1
Next Item
Debug.Print "Total number of concurrent objects on " & check_date & " = " & concurrent_items
If concurrent_items > max_concurrent_items Then max_concurrent_items = concurrent_items
Next check_date

MsgBox "Maximum number of overlapping objects = " & max_concurrent_items

End Sub

currently my regional settings do not allow me to thoroughly check the code but it should work
0

Author Comment

ID: 37018561
Thank you for your comment, akoster.  I will check this out soon and get back to you once I have implemented this into my code.
0

LVL 24

Expert Comment

ID: 37018661
Would you be expecting an answer of 3 or 4 for your example? I would have thought 4 (objects 1,3,4,6).
0

Author Comment

ID: 37018696
I would be expecting 3, which does make a difference.   I am simply using this number to determine the row height.  I am dynamically creating these shapes and need to know the "maximum overlap" at any one date.  So for my example, 3 would be the maximum number of overlaps from 10/10-10/13.
0

LVL 24

Expert Comment

ID: 37018782
OK, sorry I was being dense! I think I have a formula solution, but it's a bit long-winded (sure one of the guys here could sub it down)

If your start dates are in B1:B6, end dates (col B+10) in C1:C6, then this in D1:D6

=SUM(((\$B1<\$B\$1:\$B\$6)*(\$C1>\$B\$1:\$B\$6))+((\$B1<\$C\$1:\$C\$6)*(\$C1>\$C\$1:\$C\$6)))

array formula so use Ctrl+shift+enter

Then somewhere else

=MAX(D1:D6)

will give you the answer. It worked for your example, but no guarantee it will work every time.
0

Author Comment

ID: 37019395
Thank you guys for your help.  I modified the code slightly to fit my existing date array, but the algorithm worked perfectly for what I need.

I also tried out your idea Stephen, but I needed the solution in VBA.  Either way, thanks again for your guys' help!

Rob
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
###### Suggested Courses
Course of the Month19 days, 17 hours left to enroll