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
Start Date	Start Time	End Date	End Time	Container	Simultaneous	Overlap (mins)
01/01/2017	01/01/2017
01/01/2017	01/01/2017
01/01/2017	01/01/2017
01/01/2017	01/01/2017
01/01/2017	01/01/2017
01/01/2017	01/01/2017
01/01/2017	01/01/2017
02/01/2017	02/01/2017
02/01/2017	02/01/2017
02/01/2017	02/01/2017

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]9:15 [/TD]

[TD="align: right"]11:20 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]16:30 [/TD]

[TD="align: right"]19:25 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]14:00 [/TD]

[TD="align: right"]18:10 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]16:40 [/TD]

[TD="align: right"]17:15 [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]16:45 [/TD]

[TD="align: right"]18:00 [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]18:05 [/TD]

[TD="align: right"]18:20 [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]17:50 [/TD]

[TD="align: right"]20:30 [/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]15:45 [/TD]

[TD="align: right"]17:55 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]17:45 [/TD]

[TD="align: right"]20:00 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]19:45 [/TD]

[TD="align: right"]21:55 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]15[/TD]

</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.