1 00:00:04,840 --> 00:00:08,980 Let's take a look at our data manipulation and preparation challenge. 2 00:00:09,220 --> 00:00:13,770 You received the below information for sales agents and you need to do the following. 3 00:00:13,900 --> 00:00:20,290 Number one, create a log in user I.D. that consists of the last name followed by the first character 4 00:00:20,740 --> 00:00:21,840 of the first name. 5 00:00:22,510 --> 00:00:25,440 So here is a list of our sales agents. 6 00:00:25,450 --> 00:00:28,180 This is where we want to have the system log in. 7 00:00:28,210 --> 00:00:30,520 Let's just take a look at how much data we have. 8 00:00:30,640 --> 00:00:32,360 So control and the down arrow key 9 00:00:32,390 --> 00:00:34,590 to jump to the bottom of the data set. 10 00:00:34,780 --> 00:00:37,160 It's until line 40. 11 00:00:37,260 --> 00:00:37,620 Okay. 12 00:00:37,630 --> 00:00:46,610 So right here we want to get something that is like "BridgeS" and the next one should be "ThompsonE". 13 00:00:47,020 --> 00:00:50,150 What feature or tool can we use here? 14 00:00:51,310 --> 00:00:53,270 Flash Fill, right? 15 00:00:53,350 --> 00:00:59,470 Just gonna press enter, go to the next cell, and start typing and Flash Fill already picks it up. 16 00:01:00,040 --> 00:01:05,700 All I have to do is press enter and I have my list, so let's jump to the bottom. 17 00:01:05,710 --> 00:01:08,810 We have "HoferL" everything looks nice. 18 00:01:08,860 --> 00:01:09,400 That was it. 19 00:01:09,400 --> 00:01:14,110 Number one is done. Number two. Add the start 20 00:01:14,110 --> 00:01:16,920 year to column D. 21 00:01:17,050 --> 00:01:18,880 So the start date is here. 22 00:01:18,910 --> 00:01:22,000 We just want to get the year. What can we use here? 23 00:01:22,360 --> 00:01:28,560 Also Flash Fill. Start typing 2013 the next one is 2012. 24 00:01:28,570 --> 00:01:35,560 Now Flash Fill came and then disappeared because I continued typing. what I'm going to do is to 25 00:01:35,740 --> 00:01:39,630 use AutoFill and then to like Flash Fill from the AutoFill options. 26 00:01:39,660 --> 00:01:43,390 You can also highlight the area and use the shortcut key Control+E. 27 00:01:43,460 --> 00:01:45,900 So I'm just going to send this down. 28 00:01:45,940 --> 00:01:47,920 Now, that's AutoFill. 29 00:01:47,950 --> 00:01:54,700 Click on the AutoFill options here and select Flash Fill and we have our years. 30 00:01:54,700 --> 00:01:56,810 This is 2013, 13. 31 00:01:56,860 --> 00:01:58,310 This one is 12, 12. 32 00:01:58,470 --> 00:02:08,669 That was number two, number two is done. Number three. Add a note, legacy comment to A9. 33 00:02:08,680 --> 00:02:11,730 It says please input first name followed by last name. 34 00:02:11,770 --> 00:02:14,130 Actually I'm just going to copy this. 35 00:02:14,170 --> 00:02:15,090 So highlight this, 36 00:02:15,110 --> 00:02:23,230 press Control+c to copy, go to A9, right mouse click, and add the comment. 37 00:02:23,230 --> 00:02:30,760 So in my case I have Office 365, I have to select new note because I want to add a legacy comment here. 38 00:02:30,850 --> 00:02:37,870 If you have an older version of Excel you need to select new comment because your new comment is my 39 00:02:37,870 --> 00:02:45,430 new note. So I'll go with new note and since I copied it already I'm just gonna do Control+V and that's 40 00:02:45,430 --> 00:02:46,840 my comment right there. 41 00:02:46,840 --> 00:02:53,620 That was number 3 so every time we hover over this cell we see the comment. Number three is done. 42 00:02:53,620 --> 00:03:01,650 Number four. Add a screen tip for C9 that describes the logic behind creating this system login. 43 00:03:01,740 --> 00:03:05,020 So basically our secret, so a screen tip this time. 44 00:03:05,020 --> 00:03:09,650 Can I just right mouse click and select screen tip? 45 00:03:09,760 --> 00:03:10,440 No, I can't. 46 00:03:10,500 --> 00:03:11,720 It's not an option there. 47 00:03:12,010 --> 00:03:16,160 Screen tip is a part of the data validation tool. 48 00:03:16,210 --> 00:03:18,030 Where do we find data validation? 49 00:03:18,750 --> 00:03:24,430 It's in the data tab and it's right under the Data Tools Group here. 50 00:03:24,530 --> 00:03:28,930 Just click on this and we get to our data validation settings. 51 00:03:28,930 --> 00:03:33,320 Now I don't need to change anything in the settings I just want to add a screen tip. 52 00:03:33,400 --> 00:03:40,750 So all I have to do is add an input message here. That's it and then click on OK. And now I have a screen 53 00:03:40,750 --> 00:03:41,230 tip. 54 00:03:41,410 --> 00:03:46,180 So every time someone goes to this cell, the screen tip shows up. 55 00:03:46,180 --> 00:03:47,680 That was number four. 56 00:03:47,710 --> 00:03:57,090 Number four is done. Number five. Add data validation for start date to ensure the data is input correctly. 57 00:03:57,100 --> 00:04:05,100 Let's apply this to B10 to B50 and then ensure it works correctly if new dates are added. I'm going 58 00:04:05,100 --> 00:04:11,950 to use Control Shift down to jump to the bottom and then just hold on the shift key and expand this until 59 00:04:11,950 --> 00:04:13,730 we get to 50. 60 00:04:13,750 --> 00:04:16,170 Now I want to add a validation for this. 61 00:04:16,180 --> 00:04:18,240 So I'm going to go back to data. 62 00:04:18,399 --> 00:04:24,550 Data Validation, this time I want to go to settings because I don't want to accept any value. I want to 63 00:04:24,550 --> 00:04:31,410 make sure this is a date, so select date from this dropdown. And from data here 64 00:04:31,510 --> 00:04:36,280 it really depends on what we feel comfortable with using. 65 00:04:36,280 --> 00:04:43,420 I'm just gonna go with greater than. I could give between two specific dates but I'll just use greater than 66 00:04:43,420 --> 00:04:51,340 and put a date that I don't expect anyone to have the start date on. So I'll put 1999 67 00:04:52,120 --> 00:04:56,680 and I can add an error alert as well and then click on Ok. 68 00:04:56,680 --> 00:05:07,650 So now let's test this, Let's jump down and input a date that's valid so I'll do 3/1/2020. 69 00:05:07,690 --> 00:05:10,080 Press enter, that was accepted. 70 00:05:10,090 --> 00:05:10,680 All good. 71 00:05:11,320 --> 00:05:14,860 Let's change this to 1998. 72 00:05:14,860 --> 00:05:19,660 Press enter, "please make sure the data is input correctly" 73 00:05:19,720 --> 00:05:20,670 So I can retry, 74 00:05:20,670 --> 00:05:24,830 Cancel, and try to put a date that's valid. 75 00:05:25,120 --> 00:05:26,500 That is done. 76 00:05:26,500 --> 00:05:28,120 That was number five. 77 00:05:28,120 --> 00:05:29,740 Number five is done. 78 00:05:29,760 --> 00:05:31,090 Challenge completed.