Creating a Vacancy/Filled Census Summary

[Slizer6893]

Hello Everyone,

I have a bit of a brain-scratcher that I have been working on for a few days and most of my original methods have been quite manual so I was hoping to see if anyone had any suggestions on how to reduce the manual part of this problem.

Basically I have a bunch of transactional data that is dumped from our system that provides me ins and outs of locations and their codes. What I do each month/quarter is clean the data and create a pivot that shows a census break-down for 4 hour increments for the month.

That looks like this (This has not been broken into 4 hours yet)

[TABLE="width: 223"]
<tbody>[TR]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30-Dec[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]6 AM[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]7 AM[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]10 AM[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5 PM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]31-Dec[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12 AM[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]2 AM[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]3 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8 AM[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]1 PM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]2 PM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]6 PM[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]11 PM[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

However I do this completely manually right now since I haven't been able to identify to figure out a way to manipulate the data to so I can see how long a location is filled. Here is an example of what I see

[TABLE="width: 625"]
<tbody>[TR]
[TD]Event[/TD]
[TD]EVENT DATE/TIME[/TD]
[TD]Floor[/TD]
[TD]Location[/TD]
[TD]Sub Location[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"] 12/30/2015  02:55:00 AM[/TD]
[TD]7FB[/TD]
[TD]727[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Transferred Out[/TD]
[TD="align: right"] 12/30/2015  03:13:00 AM[/TD]
[TD]7FB[/TD]
[TD]727[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Transferred In[/TD]
[TD="align: right"] 12/30/2015  03:13:00 AM[/TD]
[TD]7FB[/TD]
[TD]725[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Transferred In[/TD]
[TD="align: right"] 12/30/2015  06:08:00 AM[/TD]
[TD]7F[/TD]
[TD]701[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Transferred Out[/TD]
[TD="align: right"] 12/30/2015  06:08:00 AM[/TD]
[TD]7FB[/TD]
[TD]725[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Transferred Out[/TD]
[TD="align: right"] 12/30/2015  07:25:00 AM[/TD]
[TD]7F[/TD]
[TD]701[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Transferred In[/TD]
[TD="align: right"] 12/30/2015  07:25:00 AM[/TD]
[TD]7F[/TD]
[TD]703[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Dismissal[/TD]
[TD="align: right"] 01/01/2016  01:00:00 PM[/TD]
[TD]7F[/TD]
[TD]703[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]

So in this example I need to determine on how often Floor 7F/Location 401/ Sub location A was filled. This goes on for 20k+ lines of various floors/location/sub-location.

The tricky part I can't seem to figure out is how to make this data work for me by saying something like if I have a filled Floor/Location/Sublocation from 12/31/2016 till 1/5/2016 that during that entire time that floor/location/sublocation is filled and would have a 1 in each 4 hour increment when viewing a census.


The ultimate goal of this is to determine peak 4 hour increments to allocate resources better.

If you need more information please ask I know this is a weird request but I can try to provide data at some point I just need to scrub a lot. The ultimate goal is to provide a pivot table or graph that can help show the varying levels of filled locations. This is re-done every month but the data is always the same format as shown above.


Thanks for any suggestions!

 

[Slizer6893]

Okay I made some big progress and here are the steps I took so far in case anyone else ever searches for this.

1. Extracted only the transactions of the data I was looking at (In this regard it was one department
2. Created a In/Out helper column to decipher a transfer in as In, Out as out, dismissal as out, etc
3. Then create a rolling tally of location/sub locations with a simple if statement
4. Totaled everything across and create a running total at each transaction time given
5. Used Mround and a pivot table to press the data into 4 hour increments and used a "MAX" in the pivot to get a peak number

Seems easier now that its done but still not the most efficient method considering it took an hour or so to sift the data.