00:00as a worker you understand the
00:01importance of what you're doing in your
00:02workplace as a teacher I understand the
00:04importance of hard work and dedication
00:06to my students so that they can get the
00:07best education that they can maybe as a
00:09Hospitality worker you understand the
00:11importance of giving your customers a
00:13good experience while they're there so
00:14that they can escape from their work
00:16life just for a little bit before they
00:18have to go back and deal with the things
00:19that we have to deal with well
00:21unfortunately sometimes we feel like
00:22we're getting taken advantage of maybe
00:24you're scheduled for example as a
00:26teacher to do more duties than other
00:28people While others do absolutely none
00:30at all getting a few favors here and
00:32there from management maybe if you're in
00:34Hospitality you find the inverse where
00:35you're not getting enough shifts or
00:37you're not getting the prime shifts
00:38again thanks to management and giving
00:41favors to other people well it's time to
00:43stop this level of exploitation within
00:45your workplace and extract data from
00:47your company to see who's being
00:49exploited and who's getting off
00:50scot-free so let's jump in now this is
00:53the duty list that was sent out to me
00:55and other staff from our school now your
00:57data is going to look completely
00:58different but I'm going to show you how
00:59to analyze this information so that you
01:02can analyze your information but if you
01:03do get a little bit mixed up and you're
01:05struggling you're welcome to send me an
01:07email and I can help you out so the
01:10first thing we need to do is just make a
01:12copy of all of this data so then we can
01:13work with our own version of this
01:15spreadsheet so up the top we'll go file
01:18and then make a copy I'm going to say my
01:24click on make a copy and now this once
01:27it loads it's going to be our personal
01:28copy we can do with this whatever we
01:30want but we need to make sure that the
01:33data is correct so to do that we're
01:35going to import all of the information
01:37from here to our new spreadsheet so I'm
01:39going to start with an equals in A1 and
01:41make sure you are in A1 equals import
01:44range now import range allows us to
01:46import information from a spreadsheet
01:48that's been shared with us now the
01:50spreadsheet URL is just the URL of the
01:53original spreadsheet so import range and
01:55then double quotation marks go back to
01:56the original spreadsheet click up the
01:58top press Ctrl C to copy it and they're
02:01back in the original spreadsheet Ctrl V
02:05close double quotation marks and then a
02:07comma the next thing is the range string
02:10well at the bottom it's called Judy list
02:11so in double quotation marks we'll write
02:13Duty list make sure it's spelled
02:15correctly with Correct capitalization
02:17exclamation mark and then A1 to in this
02:22case it's going to be M how far down
02:24does it go M60 close double quotation
02:27marks and press enter now there's going
02:29to be a few errors that's going to pop
02:31up the first one is a reference error
02:33actually the first two are going to be
02:34the reference error if we highlight over
02:36this so we Mouse over it it says do we
02:39want to connect these spreadsheets yes
02:40we do so click on allow access that
02:43doesn't change the reference error
02:45because if we highlight that it says
02:46it's trying to overwrite data in H1 but
02:49it can't H1 is over here and the reason
02:52it's not being able to is because all of
02:54these values here are set values so we
02:56just need to delete everything and when
02:58we do it's going to Auto populate so
03:01we've selected everything let's select
03:03absolutely everything
03:04press delete on all of that and all of
03:07these press delete and now all of this
03:10is directly from the original
03:12spreadsheet so if something changes in
03:14the original spreadsheet that's also
03:16going to change in our spreadsheet just
03:18to go a little bit further if you want
03:20to ensure that you are getting updates
03:22we can go back to the original
03:24spreadsheet go up to tools and then
03:27notifications settings we'll click on
03:29edit notifications click on add another
03:31notification rule we'll click on that
03:33click on notify me when any changes are
03:36made and you can choose daily digest or
03:40right away whatever you want to use
03:42click on Save and now anytime there is a
03:44change in this original spreadsheet that
03:47will send you an email to your email
03:49inbox saying there was a change and it
03:51will also update all of this information
03:53if we type something in here then that's
03:56going to lose all the information we can
03:58see that reference error again all we
04:00need to do is just delete that so that
04:02is step one making sure that we have a
04:04copyright of the original data and that
04:06we have data Integrity so that if
04:08something changes ours changes as well
04:10step two we're going to analyze all of
04:13this information and see how we can
04:15extract the names and then count the
04:18so let's go through and see what we've
04:20got all of this stuff here we don't care
04:22about these we just want to know about
04:24the names so here's some names here's
04:27some names and what we could do is
04:28extract each of these bits individually
04:30but really that's going to take a long
04:33time so instead let's just extract this
04:36information including the times
04:38and this information including the times
04:42so to do that the first thing I'm
04:45thinking is let's take the
04:47um these two sets of weeks and turn it
04:50into one big set but one big column set
04:52so instead of having them side by side
04:55they'll be downwards to do that let's
04:57just go off to the right here in o1 I'm
05:00going to type equals and then open a
05:02brace now the brace is just next to the
05:03p on your keyboard hold shift and press
05:06the first set of data we want is this
05:09information here it's week one
05:12we'll just select that information there
05:14and then press a comma
05:17week two is now here
05:20and then close the brace so all this is
05:22going to do is take bring in the
05:24information that we have over here but
05:27now instead of having it in two sets of
05:28columns week one and week two it's all
05:31now in one column one set of columns
05:35of course we don't want one set of
05:36columns we want one single column so
05:39we're going to take all of this data and
05:41turn it into one single column all in
05:44so to do that press enter to enter the
05:46cell so that we can get the formula
05:48right at the beginning of the formula
05:49we'll type in flatten with an Open
05:51Bracket and press enter now everything
05:53is in one column and just one column
05:56it's a lot of data but we're going to
06:00so let's analyze this and see what we
06:03need to do so first off we've got these
06:04blanks here we don't want any blanks so
06:06we'll be removing those secondly we've
06:08got the times we don't want any time so
06:10we need to find something that is
06:12consistent within all of the times and
06:14what I can see here is there is a hyphen
06:16in every time there is also a dot in
06:19every time and there's also numbers
06:22now none of the names have any of those
06:26so I'm just going to choose one I'm
06:28going to choose the hyphen to search for
06:30so I'm going to say if there's a hyphen
06:31in the cell then we don't want it yours
06:34might look different so if you've got
06:35maybe a semicolon like that maybe your
06:38time is set up like this again you're
06:40not going to want any of the time so
06:42you're just going to search through your
06:43data and extract anything or remove
06:46anything that doesn't have a colon in it
06:48we also have down here
06:50km and EF next to each other combined in
06:54one cell we don't want these in one cell
06:55we want these separated into km and EF
06:59so we're going to start with that I
07:01think so up the top back in our formula
07:03before this the flatten we'll go with a
07:05split we're splitting all of this stuff
07:07we're going to leave that exactly as it
07:09is don't touch it and then a comma and
07:12then the delimiter is asked delimiter is
07:14asking what do we want to split by well
07:16we want to split by the slash here so in
07:19double quotation marks we'll put that
07:21slash and press enter
07:22now this gives us a value error because
07:25split only works on one cell at a time
07:28so if we enter back into the cell and
07:30hold Ctrl shift enter that gives us this
07:33array formula at the beginning
07:35press enter and now it's going to look
07:38through the entire array and then split
07:41anything that has a slash in it these
07:43value errors are just blank values so we
07:46don't really need to worry about those
07:47those will disappear later on but now we
07:49can see down here km and EF has been
07:52split into two different cells so here's
07:54CL and ah but we don't actually want
07:56them in two different columns we want
07:58them in one column so again back up to
08:00the top press enter to enter the cell
08:02this array formula is always going to be
08:04at the very beginning so after the array
08:06formula we we want to turn all of our
08:09data here into one column how did we do
08:12it before it was with the flattened
08:14formula so using flatten this is now
08:17going to give us a whole bunch of
08:19information but we can see km and EF now
08:22in one column CL and ah are now in one
08:25column whereas before they were
08:28together like this next up we're going
08:31to remove the bits that we don't want
08:33and that will leave us just with the
08:35name codes so there's a few bits here
08:37that we don't want there's the times
08:38there's the blanks there's the value
08:40errors press enter to enter the cells
08:42from here we're going to start using our
08:44query function we're going to do this in
08:46a few steps and I'll step by it and step
08:48through it bit by bit so after the query
08:51all of this is just going to remain
08:53exactly as we wrote it we're not going
08:55to touch that that is the data we're
08:56working with after that we'll type a
08:59comma and then it's saying here what's
09:01our query going to be our query in
09:03double quotation marks is going to be
09:06select column one where column one is
09:10not null and all that's going to do is
09:13remove all of the null values all of the
09:15blanks so it did look like this with
09:18these blanks in it but now
09:20we have no more blanks next up we want
09:23to remove the value errors and the times
09:25we'll do this in two steps I'm going to
09:27first show you what we don't want to do
09:30and then we'll invert that so it does do
09:33the correct thing select call 1 where
09:35column one is not null and column 1
09:38contains a hyphen The Hyphen is in
09:42single quotation marks press enter on
09:44that and that extracts all of the times
09:46now we don't want the times we want to
09:48get rid of the times so where we wrote
09:51Call 1 contains the hyphen we'll write a
09:54not before that so that inverts it
09:57so there's no more times now we're just
10:00left with values and names
10:03now for the values the values have three
10:06parts to it it's got the symbol at the
10:09beginning the hashtag the pound symbol
10:11whatever you want to call it then it's
10:12got the word value and then a quote and
10:14then an exclamation mark now the symbols
10:17are the best parts to work with so we're
10:19going to say uh extract anything that
10:23does not have one of these symbols I'm
10:25going to choose to go with the pound
10:30so we're going to say end call 1
10:32contains the hashtag now of course this
10:35is going to give us only the errors
10:37whereas we want to remove those errors
10:39so again just before the call one
10:41contains hashtag we'll write the word
10:43not and now we get just the list of
10:49so we're almost there
10:50after we've got the list of names we
10:52want to group the names together so
10:54instead of having a I here and a I here
10:58we're going to group everything together
10:59so we only have one Ai and then on the
11:02and then a separate column saying how
11:04many times does a I pop up so back up to
11:09right at the beginning of our query
11:11where it says select column one we're
11:13going to put a comma and then we're
11:14going to say count column one so this is
11:16actually going to give us two columns we
11:18have column one and this is going to be
11:20column two now if we press enter on this
11:22it's not going to work because it's
11:24saying that we need to aggregate column
11:27one aggregate means group so right at
11:30the end we're going to say Group by
11:33press enter on that and we now get all
11:36of these now there's a time here because
11:39I put that 12 30 in earlier so let's
11:41just copy and paste the format from the
11:44one below so we've now got the list of
11:48names and how many times those names pop
11:51we want to order this by the second
11:53column because we want to see who's
11:55getting off lightly and who's getting uh
11:59so right at the end we'll say
12:02order by we can't say call to column two
12:06but we can say count of column one
12:10press enter on that and now it orders
12:13from the list to the most smt senior
12:18management team do get the most duties
12:20because they are out every day out at
12:23the gate as long as it's not raining
12:26okay we're almost there but if you want
12:28to see who has rusted on the most
12:30instead of the least right at the top at
12:32the end we'll just put the word d-e-s-c
12:36press enter and we can see that's now
12:38ordered from the most to the least so
12:42that's how we can use Query function to
12:45extract names from a roster from this
12:48roster in particular again your roster
12:50is going to be different so you need to
12:52analyze yours just like we did ours now
12:54we're not done yet because we can do a
12:56lot with this information and we're
12:57going to see how we can include people
12:59that have absolutely no roster duties
13:02but before we do that if this
13:03information has been helpful to you
13:05please make sure you leave a like and
13:07leave a comment down below maybe ask a
13:09question about what's going on or if you
13:11really feel comfortable feel free to
13:12leave a story about what's going on in
13:14your workplace and how this is going to
13:16be helpful to you so let's jump back in
13:18and see how we can go further with this
13:19information in two different ways so
13:22first off we don't want to have to
13:23scroll through this every time we want
13:25to find who's on what Duty so let's up
13:28the top put in teacher code
13:30and then below that we'll write number
13:32of Duties the teacher code will use a
13:35drop down list the drop down criteria
13:37will be dropped down from a range and
13:40then click on the window button to
13:42select the range of teachers now if you
13:43want more information about how I did
13:45that feel free to check out the video
13:46above about the new drop down chips
13:49so the teacher code here will now update
13:51with this information here and we're
13:53going to extract from this list how much
13:56each teacher is doing how much work each
13:58teacher is doing so in our number of
14:00Duties that's in P2 here we're going to
14:02use a vlookup now vlookup just looks for
14:05a value that's been selected from a
14:08range of information so the search key
14:10is going to be whatever's in P1
14:13then we'll put a comma and then the
14:15range the range will be the list of
14:17I'm going to lock that by pressing f4 on
14:21that gives us these dollar signs and
14:24the index is asking what column do you
14:27want in this case we want column two
14:30a comma and then it's saying do we want
14:32it sorted we'll put a zero for that for
14:35press enter and it says n a because
14:37nothing is selected up here if we select
14:40something GA we can see that we get
14:42number of Duties two and if we look down
14:44in the list we can see GA has two
14:47if we select HF we can see they have
14:49three and if we search down here HF has
14:52three duties so this is working exactly
14:54as intended but we can go even further
14:56by adding in some conditional formatting
14:59so let's close that off and then head
15:01over to O8 click on format conditional
15:05formatting and then the apply two range
15:08will be 08 to O in this case 055 it
15:14the format rules format cells if text
15:17contains equals P1 up here now you can
15:21see that the wrong information has been
15:23highlighted so all we need to do is put
15:25in some dollar signs around P1 and click
15:27on done and now HF has been highlighted
15:30because HF has been selected if we
15:33change this to BC or FD we can see that
15:36changes our conditional formatting and
15:38selects the right person
15:40now I really want to make this pop so
15:41green isn't that great of a color I'm
15:43going to change this color to bright
15:45orange so that we can easily see what's
15:49now along with conditional formatting of
15:51seeing where a person is we also want to
15:53focus in on how many duties they're
15:55doing so I also want this 2 here to be
15:58highlighted make sure you are clicking
16:00somewhere in the range that has
16:01conditional formatting on it so that's
16:03in 08 to o55 click on that conditional
16:06format and we're going to change the
16:08range here to 08 to p55
16:11and then down in our format rules we'll
16:13change text contains to custom formula
16:18the custom formula will be 08 equals P1
16:23now you can see that only HC here has
16:25been highlighted but if we add a dollar
16:27sign just before the o
16:30then HC and 2 has been highlighted
16:33so if we change this to GK we now get
16:36the name and the number that has been
16:40highlighted if we go LM it changes so
16:43now we can easily focus on how many
16:45duties there are and that's great but
16:47seeing just the duties just the number
16:50of Duties is uh it's helpful but we can
16:52go further because we want to see where
16:54are those duties So within the starter
16:57set here I'm going to click on the first
16:59the first Duty B in B3 click on format
17:03and go down to conditional formatting
17:05our apply two range will be B3 all the
17:09way through to in this case it's going
17:1360 format cells if text contains just
17:17like before equals P1 it with the dollar
17:20signs I'm going to change the color to
17:23bright orange so we can easily see it
17:24and we can see straight away GG pops up
17:28um there there there and if we change
17:31the GG up here to someone else let's go
17:35we can see AI has six duties we can see
17:39down here it's six and then we can see
17:42over on the left a i is here here one
17:45two three four five six including within
17:50cells that um that have two people on
17:53board so that's how we can easily see
17:55what's going on with our data but
17:57there's a little bit more because this
17:58is not the full list of teachers this is
18:01just the list of teachers that exist
18:03within this spreadsheet so next up we're
18:05going to find out who has absolutely
18:07zero duties and are getting off
18:09completely scot-free for whatever reason
18:11might be legitimate might not be so the
18:13first thing we need is a full list of
18:15stuff so over in sheet two here I have a
18:18full list of stuff this is everybody
18:20that is on the stuff
18:22the first thing we're going to do is
18:24create that query that we had before so
18:27over here I'm going to control X that's
18:30not control C control X and then over in
18:32sheet 2 Ctrl V off to the side next to
18:36our full list of stuff
18:38so this is our count and this is our
18:42we can see here that we've got errors
18:43here don't worry those will be fixed
18:46the next thing we're going to do is
18:48create a list of all the teachers that
18:50are in both lists and then how many
18:52duties each person has we'll start off
18:55with teacher code and then number of
18:58duties within teacher code we're going
19:01to start just like the trick we did
19:02right at the beginning with a brace now
19:05this brace is going to include this list
19:07here which is everybody we're going to
19:09leave some space at the bottom just
19:11include in case other people are added
19:12as well and then a semicolon and then
19:17close that press enter and we get the
19:21of these two combined it should just be
19:23the full list of this because everybody
19:25on this list should already be on this
19:27list but just in case we're going to
19:29leave it as it is and then right at the
19:31beginning we'll say unique so that we
19:33only have one of each person
19:36I also want to sort this uh we're going
19:39to sort this range the sort column will
19:41be the first column because there is
19:43only one column and then it's asking do
19:45you want it ascending I don't really
19:49um actually I do care because I want A's
19:52so now we have a full list of the
19:56next up we're going to figure out the
19:58number of Duties so we're going to start
20:02we're going to search for a b
20:05press a comma the range we'll search in
20:08is this range here I'm just going to
20:09select the first few cells and then
20:12press backspace and that select the
20:16press F4 to lock that range in place the
20:19index is asking which column do we want
20:21do we want column one or column two in
20:23this case we want column two and just
20:25like before we'll put a zero on the end
20:28now that gives us a b having one duty
20:30but if we copy this down a few then we
20:33can see we get some errors now these
20:34errors are where we have stuff that are
20:38in this list but not in this list again
20:40I'm not judging why that is uh just keep
20:43in mind that that's what these errors
20:45are so within our vlookup we're going to
20:47start with if error open a bracket we're
20:50going to leave all of this exactly as it
20:51is and then right at the end we'll put a
20:54comma and then zero and that's saying if
20:57there is an error in this vlookup then
21:00we're going to say that that person has
21:02zero duties now this only applies to the
21:05first staff member a b so we're going to
21:07change this formula so that it applies
21:09to everybody so we're going to enter the
21:10formula and then right at the beginning
21:12we'll say if and then open a bracket
21:15F2 to f equals blank then return nothing
21:22is saying if F2 to F that's this column
21:25here has nothing in it that's this part
21:29then we're going to have nothing
21:32otherwise we've got our if error our
21:34vlookup our F2 let's change that F2 to
21:37F2 to F and then hold Ctrl shift and
21:41enter to give us that array formula at
21:43the beginning press enter and we get a
21:45reference error because we need to
21:46delete all of these once we've deleted
21:48those everything pops up and we are good
21:51to go we can see the AK now has zero and
21:54all of these people have zero because
21:56they don't appear in this list now for
21:59the final step back to our duty list
22:00we're going to take all of that
22:02information and query it and then select
22:04the data that we want that's the section
22:06here backspace to give us the full
22:10put a comma and then inside our query
22:13we'll write where col1 is not null now
22:18that's going to give us an error because
22:19it's not recognizing call one so we've
22:21got two options we when we enter the
22:23cell we can either put braces around our
22:26data and that will work the other option
22:29is to change the word call one into F
22:32because f is column one press enter and
22:35that gives us the full list of
22:38um names and duties again just like
22:42before we want to sort this by column
22:44two so right at the end we'll say order
22:46by G so there we have it we have the
22:49full list of Duties and the teachers
22:52that have those duties now we can see
22:54problems at the top we can't find AI
22:56within the number of Duties because our
22:59p55 here stops too early if we look at
23:02our data set we can see the purple stops
23:05here we need to apply that to the entire
23:07list so I'm up in here I'm just going to
23:11press enter and that gives us 6 here if
23:15we go up to our drop downs go right down
23:17the bottom to find the edit button click
23:19on that and then the drop down list
23:21should be oh eight to O click on done
23:26actually uh let's just take this and
23:31so that our headers are in row seven and
23:34that our data is in row eight and lastly
23:37we want the conditional formatting so if
23:39we close that down open up our format
23:41conditional formatting and all we're
23:43going to do is just change these o55s
23:46and p55s to O and P click on done
23:51let's change p8 as well to p7
23:54click on done and now ai is selected so
23:58if we scroll down here we can see AI is
24:00selected with six duties
24:02change this to d e now we can see d e
24:06has zero duties so now you can easily
24:09see which teachers have which duties and
24:12it's easy to find them in both this list
24:15here and this list here so now you can
24:18take this information and start
24:20investigating it why do these people
24:21down here have so many duties when they
24:24also have other duties on top of these
24:26not from this list for example form time
24:28and why do these people have none at all
24:31some of them are legitimate some of them
24:33shouldn't have duties because they have
24:35other things to do in the school but
24:37there's some that are definitely getting
24:38favors going on and that doesn't apply
24:40with the workplace policies and if
24:43you've got a safe place to work you can
24:44take it to those who are in charge to
24:46point out what's going on and if that
24:48doesn't work go to your union
24:50representative so that they can approach
24:52your management and your workplace and
24:54say why are these people working super
24:56hard why are these people not working at
24:58all get the answers that you need and
25:00see if you can come to some sort of
25:02resolution do be careful with this
25:04information don't get yourself in too
25:05much trouble keep yourself safe and I
25:07will see you next time
