Meeting room data

[jappi]

Hi - hoping someone may be able to help please. This is way beyond my level of knowledge:

I have one set of data from a meeting room booking system (booked data) in one tab and another set of data from sensors in the meeting room (actual data) in another tab. I want to compare / match various elements of the 2 sets of data to determine if the booked meeting actually took place.

Sample of booked data:

Booking Date Room Event Start Event End
2/3/18 Room 10.1 8:30 am 9:30 am


Sample of actual data:

Time Room In Use
2018-03-02 08:00:00 +1000 AEST Room 10.1 0

Other things to know:
- There will be multiple rooms so I also need to match the room names across each data set.
- The booked data will only ever have one row for a meeting for that given room and start / end time
- The actual data can have many rows of data that has a time that falls within between the Event Start and Event end times from the booked data. This is because the sensors are sending data back every 2 minutes for each seat in the room. For example, if we had 4 seats in Room 10.1, each with a sensor and a 60min meeting, we would have 120 lines of data that may have a time stamp that falls between the meeting start / end time from the booked data.
- The "In Use" element on the actual data is either 0 (seat not in use) or 1 (seat in use). If we have a 1 on any of the lines of actual data that fall within the booked window, then by definition the meeting took place because at least 1 seat was occupied.
- The date / time formats shown above is as it is in Excel

So - in my dumb speak the logic would be something like:

Where actual room name = booked room name and;
actual date = booked date and;
actual time is => event start and =< event end and;
actual in use is = 1 then;
mark row of booking data with "meeting happened" else;
mark row of booking data with "meeting did not happen"

Hope this makes sense and thanks in advance for any help.

 

[RasGhul]

Is the raw data something we can convert with formulas? I had a similar scenario a few months back where the criteria was on the wrong axis for normal formulas to work.

The main problem is its going to be hard to define which hour those 2 min increments belong to. I'll experiment with some ideas for transforming the data.

 

[jappi]

The raw data to start with once converted from decimal to binary is all the 1 and 0 values you see but just in once cell. From memory the converted binary data is in blocks of 6 with a space in between each block in the single cell. I've just done text to columns to fan them out as they are now. The binary data needs to be read from right to left. So when we have a time stamp in column A of the sensor data with say 08:00 then the first reading is at 08:02 using the first 1 or 0 on the far right of the binary data set....as per the column headings I have right now.

What if we massaged the binary data into just 2 columns. One for the first 30 mins and another for the second 30 mins of the hour. Majority of meetings are usually on the hour or half hour. If we converted each 30 min block to a single "happened" or "did not happen", would that make it easier to cross check with meeting start / end time?

 

[RasGhul]

Hang on Jappi,

I may have a solution for you, I'll post it when I get home in a few hours...

 

[RasGhul]

Jappi I think we're getting there, I've checked a few blocks and its calculating correctly now to me.


Obviously your 2 min intervals are 30 columns across, I used the following to convert the binary into real times and drag down;

=IF(E2=1,$D2+E$1,"")

Sumproduct adds all matching times within the converted area to the same rows as Date & Room from Sensor Data and compares to Output criteria;

=SUMPRODUCT(('Sensor Data'!$C$2:$C$12000=Output!A2)*('Sensor Data'!$B$2:$B$12000=Output!B2)*('Sensor Data'!$AJ$2:$BM$12000>=Output!C2)*('Sensor Data'!$AJ$2:$BM$12000<=Output!D2))

*Note that sumproduct does take time to calculate as we're testing the whole matrix against your Output criteria, also as you add new rows you will need to increase $12000 to match the converted area.


Meeting Occurred was fixed to

=IF(E2>0,"Happened","Not Happened")

https://www.dropbox.com/s/drdhd9opyiyyd7o/L34 Working_jp.xlsx?dl=0

 

[jappi]

Wow...looks great RasGhul. I've run some testing and made a minor tweak to remove the = sign in the last part of your SUMPRODUCT code. This way I have a discrete end to the meeting time and am not double counting a sensor hit with the same meeting end and following start time. Once again, you've been awesome and I'm soooo grateful. I'll start building the main sheets now and hopefully we're in good shape. Thanks so much.

 

[RasGhul]

Excellent Jappi, you're welcome