Maximum concurrent event calculation

[Hellsteeth]

Hello,
I have a data set with over 60,000 lines of data per year. It contains the date and time an object was placed in a specific container and for how long it was in the container.
The more objects that were simultaneously together in the container the greater the weight and loading on the system.
I want to calculate the loading on each container to see what date and times the maximum loading occurred.
I have been trying to use both pivot tables or sumproduct along the lines of other posts in this forum such as =SUMPRODUCT(--($B$1:$B$60000<=B1),--($C$1:$C$60000>=B1)) I am however having difficulty adapting it to take into account the containers and dates.

Here is a sample of the data with some expected outcomes in the last two columns.

	A	B	C	D	E	F	G
1	Start Date	Start Time	End Date	End Time	Container	Simultaneous	Overlap (mins)
2	01/01/2017	9:15	01/01/2017	11:20	2	0	0
3	01/01/2017	16:30	01/01/2017	19:25	2	0	0
4	01/01/2017	14:00	01/01/2017	18:10	4	0	0
5	01/01/2017	16:40	01/01/2017	17:15	10	2	30
6	01/01/2017	16:45	01/01/2017	18:00	10	2	30
7	01/01/2017	18:05	01/01/2017	18:20	10	0	0
8	01/01/2017	17:50	01/01/2017	20:30	8	0	0
9	02/01/2017	15:45	02/01/2017	17:55	2	2	10
10	02/01/2017	17:45	02/01/2017	20:00	2	2	25
11	02/01/2017	19:45	02/01/2017	21:55	2	2	15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Any guidance or ideas will be kindly received.

 

[Gerald Higgins]

Hi, welcome to the board.

I think I see what you're after - it's an interesting challenge.

QUESTION - what results would you want if all data was identical EXCEPT item 11 started at 17:40 ?

 

[Hellsteeth]

Hello Gerald,
Thank you for the question on my puzzle. You ask a question that shows I had not fully anticipated all the results!
In this scenario we would now have a time period where all three objects are in container 2 simultaneously. That is clear enough I believe.
However for the overlap column you would have different overlap durations depending on how many simultaneous objects were in the container.
This will quickly become very complicated and is not a level of detail I need.
Really all I need is the time and duration of the worst case so this would be the 10 minutes between 17:45 and 17:55 when three objects are in container 2

 

[Gerald Higgins]

Hi.
Personally, I can't think of a slick solution for this.
I can only think of a kind of brute force solution, which would involve
1) Setting up a helper row for each container - don't know how many containers you have so this may or may not be practical
2) Splitting your data into discrete chunks - from the sample data, this could be 5 minute intervals, but maybe your full data is more granular than that.

Then having the helper rows calculate how many items are active in each 5 minute interval.

I've done similar things on a smaller scale which works OK, but if you have 60,000 lines of data and maybe hundreds or thousands of containers, and maybe thousands of 5 minute buckets, this could quickly become unworkable.

Maybe other people on the board have a slicker solution ?

 

[Hellsteeth]

Thanks for the pointers. If it helps - Maximum number of containers vary but are usually between 5 and 10 only. Time internals are always rounded to 5 mins. The typical duration an object is in a container is between 15 minutes and 3 hours.