Compare Lists with FILTER, COUNTIF and NOT

Quick reference

• 00:04 In the previous lesson,
• 00:05 we saw how we can use formatting to compare two lists to see the differences.
• 00:10 And I'd just like to move that idea on a little bit and
• 00:14 show you some other methods for comparing two lists of data.
• 00:18 And this lesson is all about how we can use different types of
• 00:22 formulas to compare two lists.
• 00:25 And we're going to start out in this lesson
• 00:27 by combining three different formulas to effectively get our result.
• 00:32 So what results are we looking for first of all?
• 00:35 Well, let's take a look at our data.
• 00:37 Now similar to the last example, I have a list of people who were
• 00:42 invited to an event and you can see all of those in the invitees column.
• 00:47 I then have a list of all of the people who actually attended, so
• 00:51 we have some no shows.
• 00:52 And my boss has basically asked me to compile a list of all of the people who
• 00:57 didn't show up for the event so he can send them a very harsh email.
• 01:01 And we're going to use formulas to get our result.
• 01:04 Now the first thing I'm going to do here is I'm going to put the invitees and
• 01:09 the attendees into a table, so let's press Ctrl T.
• 01:13 Yes, my table has headers, let's click on OK.
• 01:16 So now when I'm working with my formulas,
• 01:19 I'm going to be using table references as opposed to cell references.
• 01:23 And you'll see what I mean if you're not sure in a moment.
• 01:26 Now I'm going to use three formulas combined into one.
• 01:30 So I'm kind of going to reverse engineer this,
• 01:32 which will help you understand why we're doing what we're doing.
• 01:36 Now the first formula I'm going to use here is I'm going to use the COUNTIF
• 01:40 formula.
• 01:41 Now the first argument here is the range.
• 01:43 So I'm going to account the attendees range.
• 01:48 Now notice when I click to select that column, and I can do that because I have
• 01:52 this data in a table, I get table references instead of cell references.
• 01:56 So it says Table 1, which is the default name of this table, Attendees,
• 02:01 which is referring to the column that I've just selected.
• 02:05 So that is our range and our criteria is going to be the invitees column.
• 02:11 So let's close this off and see what we get.
• 02:15 We get a whole bunch of 1s and 0s, now why are we getting that?
• 02:19 Well, what's happening here is it's looking at the first invitees, so
• 02:24 George Mooney, and then it's looking for it in this list just here.
• 02:28 And if it can find it, which it can in this case
• 02:30 because George Mooney exists in both of these lists, it's giving us a one.
• 02:35 Now remember 1s and 0s in Excel stands for true and false.
• 02:40 So this first result is effectively true, George Mooney exists in both lists.
• 02:46 The same for Bradley Mitts, there he is, the same for
• 02:50 Tom Bruise, he exists in both lists as well.
• 02:53 When we get to Julio Roberts, you can see if we look for Julio in this list,
• 02:58 they don't exist, so that is why we have a 0 there instead.
• 03:02 So we have these results currently represented as 1s and 0s,
• 03:06 which isn't really what we want.
• 03:07 Now the first thing I'm going to do here is I'm going to change these 1s and
• 03:12 0s into their true or false equivalents.
• 03:14 Now to do this, I'm going to click in the first cell and we're going to go back up
• 03:19 and edit this formula and I'm going to add NOT into here.
• 03:23 That's all we're going to do.
• 03:24 We're going to close off the bracket and hit enter and
• 03:27 it's going to change these into truths or falses.
• 03:30 But I've also switched it around because I've used a not.
• 03:34 So now what we're basically saying to make this make sense is George Mooney
• 03:38 isn't in both lists.
• 03:40 Well, that is false because he exists in both.
• 03:43 The same for Bradley Mitts, the same for Tom Bruise.
• 03:47 If we get to Julio Roberts,
• 03:49 Julio Roberts doesn't exist in both lists, well, that is true.
• 03:53 So that's kind of the way you need to think about it.
• 03:56 So now that we have these true and false results,
• 04:00 what we can do is we can use one of the newer functions in Excel, the filter
• 04:04 function to filter this list and show only the people who didn't show up.
• 04:09 So let's go back to the first cell and
• 04:12 edit our formula and we're going to add FILTER in here.
• 04:17 And we're going to filter the invitees and
• 04:20 we're just simply going to close off the bracket at the end.
• 04:25 So now when we hit enter, we get a list of just the people who didn't show up.
• 04:29 And if my boss wanted me to go a stage further and sort this list alphabetically,
• 04:34 this is where I can use another one of the newer Excel functions.
• 04:38 I can go back up and edit my formula and
• 04:41 add a SORT onto the beginning here, The default is to sort A to Z.
• 04:45 So I'm not going to add anything else, hit enter, and
• 04:49 I now have those results sorted alphabetically A to Z.
• 04:52 And the cool thing about this is that if anything changes,
• 04:56 the formula will automatically update.
• 04:59 So if I was to take, for example, Reese Witherfork, I'm just going to copy and
• 05:04 paste her into attendees, she then gets removed from that no show list.
• 05:10 So everything's dynamic and everything updates.
• 05:13 So that is the first way that you can use formulas to compare two lists.

Lesson notes are only available for subscribers.