Meeting room data

jappi

New Member
Joined
Sep 7, 2006
Messages
17
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
611
Hi jappi,

It will be easier for us to help you if you can post some sample data or how you would like the output to be.
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
611
Hi Jappi,

I used helper columns on “Actual Data Sample” to convert date output, see if you can use this;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Time</td><td style="font-weight: bold;text-align: center;;">Room</td><td style="font-weight: bold;text-align: center;;">In Use</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Time</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">2018-03-02 08:00:00 +1000 AEST</td><td style=";">Room 10.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2/03/2018</td><td style="text-align: right;;">8:00 AM</td></tr></tbody></table><p style="width:14.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Actual Data Sample</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=DATE(<font color="Blue">LEFT(<font color="Red">A2,4</font>),MID(<font color="Red">A2,6,2</font>),MID(<font color="Red">A2,9,2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=TIME(<font color="Blue">MID(<font color="Red">A2,12,2</font>),MID(<font color="Red">A2,15,2</font>),MID(<font color="Red">A2,18,2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />



<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Booking Date</td><td style=";">Room</td><td style=";">Event Start</td><td style=";">Event End</td><td style=";">In use</td><td style=";">Sensor Counts</td><td style="text-align: center;;">Meetings Occurred</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">2/03/2018</td><td style=";">Room 10.1</td><td style="text-align: right;;">8:30 AM</td><td style="text-align: right;;">9:30 AM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td><td style="text-align: center;background-color: #92D050;;">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2/03/2018</td><td style=";">Room 10.1</td><td style="text-align: right;;">10:00 AM</td><td style="text-align: right;;">10:45 AM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style="text-align: center;background-color: #92D050;;">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">2/03/2018</td><td style=";">Room 10.1</td><td style="text-align: right;;">11:30 AM</td><td style="text-align: right;;">12:00 PM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: center;background-color: #92D050;;">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2/03/2018</td><td style=";">Room 10.1</td><td style="text-align: right;;">1:00 PM</td><td style="text-align: right;;">2:00 PM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: center;background-color: #92D050;;">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">2/03/2018</td><td style=";">Room 10.1</td><td style="text-align: right;;">2:30 PM</td><td style="text-align: right;;">3:30 PM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: center;background-color: #92D050;;">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">2/03/2018</td><td style=";">Room 10.1</td><td style="text-align: right;;">3:30 PM</td><td style="text-align: right;;">4:00 PM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: center;background-color: #92D050;;">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">2/03/2018</td><td style=";">Room 10.1</td><td style="text-align: right;;">4:00 PM</td><td style="text-align: right;;">4:30 PM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: center;background-color: #92D050;;">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">2/03/2018</td><td style=";">Room 10.1</td><td style="text-align: right;;">4:30 PM</td><td style="text-align: right;;">5:30 PM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: center;background-color: #92D050;;">No</td></tr></tbody></table><p style="width:10.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Output Sample</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">'Actual Data Sample'!$D$2:$D$145,A2,'Actual Data Sample'!$B$2:$B$145,B2,'Actual Data Sample'!$C$2:$C$145,E2,'Actual Data Sample'!$E$2:$E$145,">="&C2,'Actual Data Sample'!$E$2:$E$145,"<="&D2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIFS(<font color="Red">'Actual Data Sample'!$D$2:$D$145,A2,'Actual Data Sample'!$B$2:$B$145,B2,'Actual Data Sample'!$C$2:$C$145,E2,'Actual Data Sample'!$E$2:$E$145,">="&C2,'Actual Data Sample'!$E$2:$E$145,"<="&D2</font>)>0,"Yes","No"</font>)</td></tr></tbody></table></td></tr></table><br />
 

jappi

New Member
Joined
Sep 7, 2006
Messages
17

ADVERTISEMENT

Thanks so much RasGhul - this is a huge help. Your version of the output sample above also has column E which was not in my version and is now used by your formulas. Can you please advise what formula you have in column E on your output sample?

Thanks again for all your help
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
611
Hi Jappi,

The E column is just number 1s entered in that column as that is your part of your sensor criteria. Glad I could help.
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
611

ADVERTISEMENT

If you would prefer not to have column E I could hard wire it into the formula?
 

jappi

New Member
Joined
Sep 7, 2006
Messages
17
ok - I may be misunderstanding. The output sample is based on the original "booked" dataset. This dataset in its original form does not have the 1 and 0 values from the sensors, the "actual" dataset does. So in order to have column E as you have it the the output data, those values have to be derived by formulas from the "actual" dataset.

I don't need to see that column in the output sample but without it the formulas don't work, and as it seems to me, the values in column E are not correct unless they are properly calculated from the "actual" data. I hope that makes sense and thanks for your patience and help!
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
611
Yes this formula calculates directly from the Actual data set, if you only want the yes and no output use;

Code:
=IF(COUNTIFS('Actual Data Sample'!$D$2:$D$145,A2,'Actual Data Sample'!$B$2:$B$145,B2,'Actual Data Sample'!$C$2:$C$145,1,'Actual Data Sample'!$E$2:$E$145,">="&C2,'Actual Data Sample'!$E$2:$E$145,"<="&D2)>0,"Yes","No")

*Note you will still need the helper columns to get the Date & Time values for the formula.
 

jappi

New Member
Joined
Sep 7, 2006
Messages
17
Sorry RasGhul - please bear with me and the dumb questions. Im basically getting everything you have done except for one part which I'd like to use......I'm just going back to your first response, specifically the Output sample and your column "E" for "In Use". You only gave me formulas for columns F & G and the "In Use" column was not part of the original Booked data sample which is the basis for the Output sample. Can you please explain which formula you have used for column E on the output sample? I'm just struggling to understand your point above where you say "The E column is just number 1s entered in that column as that is your part of your sensor criteria". If you look at the Output sample and rows 6 down, they have a "In Use" value of 1 which means the room was in use but a sensor count of 0 - this is a conflict....even though column G appears to arrive at the right conclusion.

Thanks for you patience and help.....
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,040
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top