Excel Magic Trick 703: Extract Records Multiple Criteria (AND OR logic) Filter, Formula, Adv. Filter

welcome to excel magic trick number 703 hey if you want to download this workbook and follow along click on my youtube channel then click on my college website link and you can download the world okay so my trick 703 to 706 well we have a big comprehensive video here here are the topics where the goal all the different things we're doing or all because we want to extract records with six criteria so here's our data set we want to use six different criteria to extract some records and put them somewhere else we're going to do six criteria but everything we learn here could be applied to two or more or even one we're going to see how to do it with a filter advanced filter array formula a 2010 non array formula in the process we'll see how to work date criteria for all of these and criteria or criteria between criteria and even boolean logic so here's our data set now extracting data is one of the more common questions asked and we're going to see filter that is by far the most popular and easiest to do sometimes it takes a lot of steps and sometimes people are building applications for other people that don't know how to use filter so that might not work we'll see how to use advanced filter formulas or ultimately the most complex but they are the most dynamic and easy for perhaps people who don't know how to use filter and other things like that filter how do you turn on filter data ribbon or in earlier versions the data menu and then filter or control shift L so our criteria we have date we have greater than or equal to the 18th less than or equal to the 23rd this is called between criteria for filter and the idea is that it's between these two dates then we have sales reps ooh so in this column it needs to match sooo customer PC product organic cheese so really there's 1 2 3 4 5 those are all and so we're going to ask the question all of these have to be true for a particular record but then we have or criteria what if we want to see customer PCC in whole foods markets so for example this record matches because it's PCC this record also matches because it's an or it's either one or the other we'll see how to do this and between an or and all three methods alright filter super easy you click the drop down and go to date filters my screen this never works very well when I'm shooting this by date filters before an earlier versions you have to go to custom and then there's our dialog box and we say I click the wrong one in earlier versions you actually have this and you have to select is after we want is after or equal to and then is before or equal to and so the date will just select the date the seven the 18th is our criteria and the 23rd and then we click ok now that's filter right so filter just filters the records in place you can see the blue tells you that there are some hidden rows and our next criteria is su so we simply do our drop-down and then select down here off to the side you can see we can uncheck all and then select su click OK here we have or this is great this really is much easier than when you see how we do it in the formula we just want PCC and Whole Foods Market one or the other notice this is a column here and these are mutually exclusive categories there's a record and it's never going to be both PCC and whole food markets international so so far we've done in essence one criteria here between with the filter but really it was two three four now we need organic cheese we simply select down at the bottom organic cheese and then click OK which is off the screen down there and there you have your filtered data really a lot of people in the world do filtering or do extract records this way because it's so easy it doesn't take much to learn how to use these filters and when you highlight it copy notice I used ctrl C there but notice only the cells that are not hidden are being copied right you can tell because these are visible cells the dancing ants are marching around only the visible cells once you've copied you come over you click in one cell and then control V so that is by far the most popular method for extracting data and certainly it is easy now advanced filter now I'm going to come over here and I'm going to use control shift L it's just a toggle to unfuck filter that now notice I didn't put any records over here I'm very carefully placed them down here and when we do our formulas in just a moment we're never going to put anything here because when you filter it hides rows now advanced filter advanced filter is just a little bit different and often times people do it when they have lots of complex criteria the only trick to advanced filter is the difference between an and or this is an an and criteria is always on one line so sales rep this and this it means this has to be true and this has to be true for a particular record like this one right here but if you haven't or you have to jump down on line now if you just have or you just use different lines but here we have and and or so this is our or so because we have an end we had to repeat all of the data so in essence for advanced filter you have to repeat everything whoops repeat everything here and here and just change these two items so that makes it and and or okay the other thing is when you're doing a between criteria like this you have to repeat the field names for advanced filter still further when you have your names here this defines what goes here if you don't have the the exact same field name including no extra spaces or anything it won't work so all of these have to be exactly the same so we're going to repeat the date put the two dates and we're ready to go we click in our data set and we go to data there it is in the earlier versions data filter advanced filter right the key for shortcut in this version is alt aq filter list in place now I want to copy it to another location it's got the right data set I'm going to get my criteria gotta include the field names and the criteria and and or copy - I'm going to select this cell right there and then click OK just like that we have it so some people like advanced filter because you can explicitly see all the criteria here and once you have it set up right and you know how to do alt aq it's pretty darn easy to just dump it down there so that's filter' and advanced filter' now on to formulas which are a lot more complex and this is going to be great I've done other videos for data extract never one with six criteria and we will get to see how to mix and and/or criteria using boolean logic boolean math to get our trues and falses for both and and or now I'm going to actually hide some rows here let's see if I can now right-click hide all right okay so we can kind of see everything here we have our criteria our extract Ariat now if this is a huge data set you don't obviously put it somewhere else but this is just squishing it on the screen and I have a small data set so we can see how everything works the first thing is if you're extracting data right and you want this formula dynamic so anytime you change anything up here it changes down here and that's the main reason that people you know learn how to do these array formulas or go have someone else do it for them because it is so automatic you just as soon as you change this everything changes you don't have to do advanced filter or anything all right first we're going to have to count right we already can see from our other two methods there's two records so we need a dynamic count here remember there's six criteria and one of them's in or so let's see how to do this we're going to do some product and we're not going to use double negative like we usually do we're just going to multiply using boolean logic all the arrays of trues and falses let's just start at the beginning by the way and oops didn't mean to do that and criteria when you're using boolean logic will use multiplying this array of trues and falses times this array of trues and falses when you're doing or you add and these are mutually exclusive categories which means we're never going to get a count of two which makes a little bit easier all right our first logical test is hey is anything in the date column so I'm going to click there ctrl shift down and I'm going to hit f4 because we're going to use the same logic down here in our data extract formula whenever that's equal to I'm sorry greater than or equal to the low and date and I'm going to hit f4 on that alright so there's our first series of trues and falses and you know we can highlight this and hit f9 highlight this and hit f9 and you see you get your trues and falses and we'll use the same set up for each one of these in multiply them and only when you get a true times a true in the same position and the next a will you get a true over also multiplying is four and control-z alright so we're going to multiply and then another one and it's same range right here the date range and this is less than or equal to this F four times in the next condition we're talking about sales rep so I come down here control shift down arrow f4 anytime that is equal to sue f4 okay so so far we have three and criterias using multiplying times the next one is oh we have an or now I'm going to go ahead and do this product one first so we get all the ands in a row it doesn't matter at all though in fact forget it I'm not gonna do that I'm going to do the or now multiplying is for an we're going to have to do two separate tests so I'm going in open parenthesis and go down and get Boop this column right here F for any time that is equal to I'm going to copy that because I'm going to use that again this F four be sure to lock all these because we're going to use them down below and now I close parenthesis and then I plus now the reason you have to close parenthesis because the order of operations when Excel evaluates equal sign and plus plus comes before equal sign so we want to do all the truths and falses before so that's why we had to put it in parenthesis to force the equal operation to happen before the plus alright and then control V and I'm going to say is equal to that one so notice we're adding here f4 to lock it now get this if there's a true here for PCC The Associated position over here is never going to get a true so we're always going to get true plus false when when you add those together it's 1 anytime we get a true over here Whole Foods Market International we're never going to get one over here so we're gonna get one plus zero now if you happen to have overlapping categories which means they're not mutually exclusive you'd have to do something different you'd simply because you could get a count of two you'd simply have to take this and say anytime that's greater than equal to one no sorry greater than zero but that's not the case here right because we could get a two or one if these were not mutually exclusive but just in case you have that situation that's it to you alright so there it is we can even i'm going to close print this is on this and highlight this and hit the f9 key you can see we get a bunch of throughs and fog of truths meaning they're either/or so it looks like there's only one record that's not PCC or whole food markets control Z now one trick that we're going to have to do here this is an or right what if since we want this dynamic what if we wanted to delete PCC or this that would leave a blank here well that would cause no problems whatsoever if we deleted this and this was a blank then the formula would say is anything in here blank the problem might arise if you have blanks here now if your data set has never have blanks then this works perfect but just in case it does all we have to do is add another logical test here which is or anything down over there equal to blank so it are actually not blank so I'm going to say for a true so I'm going to say x and then say anything here it has to be an F for not less than greater than blank double quote has to be not blank to be included and that little part right here is just in case we delete any of these right instead maybe one of somebody later just wants to see just PCC all right now so we have our ands and our ORS finally we have our last little organic cheese one here so we times open parenthesis and this one right here anytime that one is equal to this one f4 alright so there we have it and the act of any operation on a boolean true/false will convert it to ones and zeros so for example double negative divided by one times when we're multiplying so anytime we don't even need the one just moult the act of multiplying will give us all the ones and zero so that we want now if I highlight this and hit f9 you can see record number two and four should be the ones that we extracted and sure enough that one and that one right there are the ones that are extracted so this is accounting because we're this formula down here is going to depend on the fact that we counted them in advance control-z now while I have a have it highlighted I'm going to copy because we're going to use that this exact logical structure that collapses down to zeros and ones will be used down here enter so we have to see if arrow is to change this then there would be only one and a blank there control-z now here's our formula to extract equals if and I'm going to use rows our number incrementer I'm sitting in be 18 so be dollar sign 18 colon be 18 as we copy this down it will increment the numbers 1 2 3 4 etc anytime that count is greater than this f4 then we want to show a blank that means when we get past Row 2 1 2 all these will be blank otherwise and now we need to do our in essence look up the array now think about this we have date sales rep so we want to extract all of the data for all the records so in this column we're only interested in that right there f4 once and twice that way the row is locked so when we copy our formula down it's locked on this but when we copy it this way it moves to the next column okay there's our extract area or lookup in essence now rownumber we have lots of row numbers or actually we have to in our case we have more than one so we need to use the small function to successively get the earliest row number then the next earliest etc now the array here we're going to see if and our logical test remember we got a string of zeros and ones from this formula up here that indicated perfectly the position there was a one at the second record and a one at the fourth record so we can use inside the small if and just paste that whole big thing right there that's pretty amazing that's got and N or criteria so that's the logical test well what in the world do we want to go into the index because remember we're right there the row number and the index needs a row number here well sure enough we need the row number so we're just going to get all the row numbers because the small has an if they're right with exactly the logic we need so row of and you can pick whichever one you want I'm going to pick that one the first one f4 now that won't work because that's row 32 31 so we subtract from it row of 30 F for now that's 30 minus 30 so we have to add one back in and that'll give us our string of 1 2 3 4 etc there's the value of true we don't need false so we close the parenthesis finally as we go down we need to extract the right row numbers so we simply use our formula incrementer again right there k close parenthesis on the small closed parenthesis on the index because we don't need the column and close parenthesis on the if that is the false control shift enter then i'm going to copy it over i'm going to point to the fill tag and say fill without formatting i'm going to ctrl shift 4 to add some dollars there and highlight all the form is and copy them down now let's test we can see control z let's change this to the 26 and that's why people like to use formulas because it's just as automatic as you can get now control Z one out one last method and just go actually going to scoop this like this I put it into edit mode highlight copy escape come over here I'm going to paste that over there and then I'm going to copy this whole thing because this formula was entered with ctrl shift enter because it's got all sorts of arrays in it that's an array formula you can see the curly brackets are automatically added in but in 2010 we have an alternative to the small function with all those arrays I'm going to control V and what we're interested in is the the logical two parts instead of using the small if construction we can use the aggregate function which M does in essence the small for us and we won't have to use control shift enter I'm going to highlight all of that logical test right - they're the same piece we had before and I'm going to control CC that opens up the clipboard I'm actually going to clear ctrl C if you don't have it open then you have to go there and use that and I'm going to highlight this row section just too right there ctrl C now I have two parts and I'm going to replace all of this up to the small m and hit the Delete key because that this is the part that requires control shift enter delete and now instead of small I'm going to use aggregate and aggregate has as one of its options you can see right there 15 is small okay and then comma since we're going to have some potential errors we're going to use six down here in that drop down it says ignore errors and then comma notice it says array right there the aggregate if you take the row part of our array formula in parentheses and divide it by all the trues and falses it will give us an array of the row numbers we need so I'm going to in open parentheses and I'm going to click on O the row number right here okay so I got that inside of parentheses close parentheses divided by and then open parenthesis and this huge thing we have here close parenthesis I don't know why that index function I may be doing something wrong here deck it oh there's a greata there's five we got that divided by that and I close parenthesis now yeah I'm in the wrong aggregate so okay there's the array I got that okay so I have to backspace I have one too many okay there we go the brown one and the brown one and then this one right here all the way to there and so now I'm still in the array so that's pretty tricky right there that's why these screen tips just help I was warned there because I was seeing the at of the index screen tip and that wasn't the right place and I had an extra parenthesis all right and then finally for the K we have our rows all the rest is the same and this I just hit enter copy it over smart tag fill without formatting ctrl shift four and then copy it down now I'm going to test this a little one more time for the aggregate six come over here see if that works and sure enough so there you have it can you believe it there's so many different ways we did filter by far the easiest I use it all the time advanced filter again pretty awesome if you have lots of criteria and finally to one array formula with and and or criteria and the aggregate function as well all right we'll see you next trick

Channel: ExcelIsFun

Views: 256,374

Rating: 4.8636365 out of 5

Keywords: Excel, 2007, 2010, Extract, Records, Multiple, Criteria, AND, OR, logic, Filter, Array, Formula, Advanced, Date, Between, Two, Mike, Gel, excelisfun, Girvin, Highline, Community, College, Slaying, Dragons, Boolean, the, functions, SUMPRODUCT, IF, ROWS, INDEX, SMAL, ROW, AGGREGATE, function

Id: 9jmNEqrfrZs

Length: 23min 31sec (1411 seconds)

Published: Fri Sep 03 2010