Excel Magic Trick 1226: Compare 2 Lists, Extract Items In List 2 That are NOT in List 1 (6 Examples)

welcome to Excel magic trick number 1226 hey if you want to download this file Excel metric 1226 to 1227 and follow along click on the link below the video we have an epic video here we want to talk about comparing two lists and finding the items in one of the lists that are not in the other list now I got two questions yesterday both asking the same question in essence but they had slightly different details here here was one of the questions I have two lists of people's name one list is shorter than the other I want to extract the names of people that are not on the shorter list now the way we're going to solve this and the method we're going to use it doesn't matter if one list is longer or shorter than the other the concept is simple we have this list right here list two in this example and we want to find the items in this list that are not over in the other list and extract them let's go over to the sheet one two to six email here's another question on the same day email addresses column one has two hundred addresses and I just have a few here for this example column two as a hundred and eighty so in this one there's more addresses in the first one but the question is of those listed in column one I want to find the twenty addresses in column one that are not in column two now the same exact solution is going to be used but our question here is please give me all the items from list one that are not in list two now I want to go back over to one two to six because we want to look at three different methods and all three methods are really awesome and have their place and purpose then when we get over to the email one I'll show you which one is my favorite now the easiest method is simply to use the match function and then sort the list and copy and paste and that's what most people do now all of the solutions method one will be the three step process method 2 will be an awesome advanced filter and then method number 3 will actually be an array formula that you only want to use when you want the process to be automatic and dynamic because it's the most complex but all three solutions have one awesome function in common the match function the essence of comparing two lists always has to do with the match function all right so this first example we're going to add a helper column and here's what we're going to use we're going to use the match now the match function is a lookup function but it does something special if I say hey match go look up chin and that'll be a relative cell reference comma the lookup array is the table we want to look it up within I'm going to hit the f4 key to lock that now what match is doing is match is looking up chin over here and match tells you the relative position so when it looks up chin one two three it will report three as the relative position now I do have to do comma and I'm going to use 0 for exact match control-enter there we go there's the position double click and send it down gigi is in the first position Jo is in the second position but check this out this is what we're interested in and a is maxing I couldn't find it it was not available now what we knew next is we actually sort the helper column so I'm going to click in one cell and notice even when I have these labels at the top I have empty cells all the way around because sorting is a data analysis feature that needs a proper data set field names at the top empty cells all the way around so I click in any one cell in the column I want to sort and I'm doing Z to a which brings the errors up to the top then I simply do step three which is to copy and paste that's pretty cool now let's go look at another example this is going to involve advanced filter now notice if we wanted we could actually use this very same formula down here in a helper column and then use advanced filter because advanced filter can actually automatically take stuff and dump it somewhere else but this trick is so awesome because you actually don't even have to add the helper column the same formula that you would put right here and copy down just like we did in the example above you actually put it in a separate cell and keep the cell above it empty and then use that range as your criteria so here it is equals and I'm going to start with the match just like we did before remember we had to say hey is the first item relative cell reference and list two comma ru over in our other table f4 comma zero close parenthesis now watch this I'm going to copy this in edit mode and enter it there then I'm going to come over here and edit mode control V control enter and double click and send it down what advanced filter will do when you have a formula criteria is it will take the formula and in memory copy it down as if it were a helper column so what I did here is totally unnecessary now while we have this here this will not work because we're interested in the n/a and advanced filter' will think that the numbers are truth so we need to actually amend the formula and ask the question is the result from match and n/a and the way you do that is with is n/a that's a pretty smart name for this function that says hey ru and na now this formula with the is n/a wrapped around match will deliver a true or false only when its season is na will it give us the truth that will be the trigger for advanced filter to extract it now I'm going to delete this because remember the beauty of advanced filter criteria with a formula is that it will copy the formula down that column in memory now notice the false because the very first one is false but as we saw when we copied it down we got it true there now click in a single cell go to data here's advanced filter now you can click on that or you can use the keyboard in 2007 or later alt aq that opens up advanced filter we do not want to filter we want a copy to another location it got the list range right because we have empty cells all the way around and here's the beauty of advanced filter I simply highlight the empty cell above the formula and the formula that advanced filter is going to copy down in memory and then I say where I want it to copy to I'm going to click over here now when I click OK just like that and now if I were to change any of this like I were to come over here and call this tum a now we have a different situation you can see the very first one here chin is no longer in that list and now when we invoke advanced filter again I'll take you and I say copy to click OK and just like that it got the results now that's sort of automatic right something changed and it totally updated it but you do have to know to open up advanced filter now that's pretty cool advanced filter now the next example we want to see is a ray phone and this is going to get pretty complicated and it's only when you want your source data to have somebody change something and then you don't do anything the list just appears that's when you use formulas now I'm going to call this something like extract names now before we even start I want to come over here and I'm going to highlight these two cells and right clicking on the mini toolbar use my format painter and then click poop right there because before I create the array for me I want to count how many items in list two that are not over in lift one and the beauty is is we can use that same is in a and then inside of isn't a match now remember we want all of them in our last formula we click in the actual single cell that's not going to work right here in lookup value or give it all of the lists too that means we're in a function argument or a operation and match will spit out many positions and na s and is na will evaluate it and give us many trues and falses now I'm actually going to lock this not that I'm going to copy it here but I'm actually going to copy this whole formula element and use it in our array formula later when we're going to need to copy it comma the lookup ray Group F for comma and then 0 close parentheses now right now if I were to highlight the match remember that was a function argument array operation right there because we gave it a bunch of answers so now when we evaluate it with the f9 key you can see it gives us exactly what we got when we did our helper column above control Z now I'm going to close this off highlight just to look f9 there's our trues and falses now we need to add all those truths ctrl Z I'm going to use some product some product can handle array operations without using ctrl shift enter but guess what some product cannot see trues and falses so i have to convert them to ones and zeros and i'm going to do that with double negative if I were to highlight that whole array and hit f9 there we have our numbers to add ctrl Z now watch this I'm going to copy this ctrl C in edit mode close parentheses on the sum-product and enter now there is our count now I'm going to come over here and we're going to start our array formula now equals index is a lookup function and remember I'm trying to get names from list two that are not over here so I highlight the whole range and f4 comma and now the row number I'm gonna need it looks like two three four and then a five and then a six and then a seven so the way we're going to do this is we're going to need to use and I'm using an Excel 2010 or later function aggregate if you don't have or later than you have to use the small function now the aggregate is beautiful because it'll handle array operations without control shift enter and look at that one of the functions the first argument function number function number 15 here is small which is what we're going to need to use in essence we're going to use the small function to get the first relative position second and then third relative position comma options we need to ignore errors because we're going to have a divide by zero error comma and then that's the array argument that can handle arrays now I'm going to use this little part right here control V but notice that this gives me if I highlight this in f9 it just gives me trues and falses I really need position 2 5 and 7 so I'm actually going to have to from this list construct an array of relative positions meaning 1 2 3 4 5 6 7 and then the trues and falses will extract from that 1 2 3 4 5 6 7 the to the 5 and the 7 so control Z so right at the beginning before is na I'm going to open parenthesis and say hey give me the row of this whole list f4 close parentheses now if I highlight the row part that's going to give me 39 40 etc if I hit f9 you can see that's not what we want control Z so from the row with function argument array operation giving me all of the rows I'm going to need to subtract row of the first one f4 close parentheses now if I were to highlight the row with all the rows minus the row of a single cell f9 that first one where I get my 0 is 39 minus 39 so control Z I'm going to have to add one back in plus 1 close parenthesis now in the book I wrote called control shift enter mastering array array farmers actually do this formula and the aggregate and the index and show you how to use small but we talked about this little formula element as usually the most robust way to create an array of relative positions like this now if I highlight this and hit the f9 you can see now I have my 1 2 3 4 5 6 7 that is beautiful now I need to filter out to only get the two the 5 and the 7 ctrl Z so I divide by that is na that entire thing in the array argument of aggregate when I hit f9 it gives me a filtered list of the relative positions of the items that are from list 2 that are not in list 1 2 oh I guess it's 6 because su isn't there I kept saying 5 so it's 6 and 7 all right ctrl Z now I have to come and comma to get to the K because the small function needs to know which of those relative positions the first one the second one the third one so I'm actually going to do another little formula element using the Rose function and I'm sitting in F 39 so I'm going to say F dollar sign 39 : F 39 this little formula element has the 39 locked here but not the 39 here so it's an expandable range as I copy down it will give me the numbers 1 2 3 4 so now I have my K close parentheses that whole thing right there can you believe it is just going to give me a row number if I hit f9 because I'm in the first position in this range I'm going to copy through it gives me a to ctrl Z now close parenthesis I don't need column number ctrl enter now watch this I copy it down and we'll get rid of those numbers in just a minute but check this out that whole gigantic aggregate is just a huge array formula when I f9 to give me the relative position to find the item unless - that is not in list 1 control z escape now I need to come up here and I want to turn those No sometimes you'll see people use if air on huge array formulas like this don't do it the reason why is if air this value has to run the huge array formula every single time determine if there's an error or not so in any case when you have an alternative logical to test to determine when to either run the array formula or not run it you use that so we do have an alternative test I'm going to say if and I already have the little formula element if the rows right here if one two three four five has I copied down is greater than my count and notice I can't get to that so I'm going to click on that and down arrow and then hit f4 so right now any time one two three four five is greater than three well that means I'm past the third row so when I type a comma the value of true is show nothing that's double quote double quote that's the syntax in a formula to show nothing otherwise the value of false has run the formula the reason this is so efficient it's because now we're running this in every cell we do not have to run index in every cell like you would with if air so when this comes out true meaning we're past Row 3 it's just going to dump this zero length text ring and show nothing and not run the formula now I can come to the end closed parenthesis and again I don't have to use control shift enter because I used aggregate control enter double click and send it down now I actually want to show you the what you have to do in 2007 or earlier you'll have to use in the index row number instead of aggregate you'll have to use small if is in a match and then the logical test there is that whole little bit and then of course in the K for small this formula requires control shift enter we have a bunch of array calculations inside the if and no matter how you slice it you have to the keystroke control shift and enter to put this formula into the cell when you use control shift enter that you tell in Excel to calculate this array formula up in the formula bar you can see those curly brackets at the beginning in the end that's Excel telling you that it understood this is an array formula and then we could copy it down now the beauty of array formulas of course is that as soon as we change any name it just totally updates alright so while one two three I do want to look at one last example I'm going to go over to one two six email and the question here is extracting any emails in list one that are not in list two and my favorite method if it doesn't have to be automatic is simply to use advanced filter so we're going to use is in a match now we have a slightly different situation here because it's the items in list one that we want to find and extract so I'm gonna say hey look up the first item in that list comma look it up within list 2 F 4 comma 0 close parentheses close parentheses remember that advanced filter will take this and copy it down the column in memory so when I enter this click in the cell alt aq copy to another location criteria range empty cell formula copy to I'm going to try cell F 12 when I click OK just like that I have the emails from column 1 that are not in column 2 now something very important about advanced filter that I didn't show you back on the last sheet is I'm going to put something below the extract area and watch what happens I'm not sure what version it was in 2007 or 10 that this started to happen because in earlier versions this didn't happen if I rerun the advanced filter it will replace everything here but it also replaces everything below so you don't want any important data below if I click in here in all take you and I'm just going to run the same one again not changing the criteria when I click OK you could see that four disappeared so when you run advanced filter below the extract area that means anywhere down below don't have anything important now one last thing this whole video is about comparing to lists and finding things that are not in a list but what if you wanted to compare two lists and say please only give me the ones that are in both lists it's no problem solution one two and three all you have to do is change is an A two is number now actually the array formula you have to change it to is number but method one with sort and advanced filter' you can actually just use the match but I'm going to show you both here is number now we're going to get a true only when the item is in both lists so if I was to rerun all take you select copy to and click OK now I get only the items that are in both lists now for method one and two where we used sorting and then advanced filter here we actually only have to have match because if the sorting one will bring the errors up to the top but filter will see any nonzero number as true and ignore the errors so all I have to do is have match now once I've done only match it's interested in when there's items in both lists so I'm actually going to remove this just to prove to this and you're ready alt aq copy to another range and boom so boom there if you really are interested in both of them use only match now back over here on one to six we saw the array formula in here for the array for me you'd have to put the is number but that an awesome array formula when everything has to be automated we saw the advanced filter and we saw match the essence of all three solutions and then sort and copy-paste wow that was an epic video we'll see you next video

Channel: ExcelIsFun

Views: 71,799

Rating: 4.8925037 out of 5

Keywords: Excel, Microsoft Excel, Highline College, Mike Girvin, excelisfun, Michael Girvin, Mike excelisfun Girvin, Slaying Excel Dragons, Excel Magic Tricks, Ctrl Shift Enter Mastering Excel Array Formulas, Array Formulas, Excel Compare 2 Lists, Excel Compare Two Lists, Compare 2 tables, Compare Two Tables, Extract Records Not In Other List Table, Get items not in List, Advanced Filter to compare two lists, INDEX function, MATCH function, MATCH to Compare Two Lists, AGGREGATE function

Id: 9h1omv60MCA

Length: 22min 32sec (1352 seconds)

Published: Fri Aug 14 2015