[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
6
Medium Priority
?
433 Views
Last Modified: 2012-05-12
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":
 Overlap Example
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
Comment
Question by:RobStl
  • 3
  • 2
6 Comments
 
LVL 19

Accepted Solution

by:
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

Open in new window


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

Author Comment

by:RobStl
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

by:StephenJR
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:RobStl
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

by:StephenJR
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

by:RobStl
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Article by: evilrix
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…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question