FILTER function AND Logical Test made Easy with BYROW & AND functions! Excel Magic Trick 1912

Welcome to Excel Magic Trick 1912. And in this video, we got to talk about the filter function and how to create an and logical test to filter and then extract records to another part of the worksheet. Hey, this video we're doing and next video we'll see how to do the or logical test. And in both I'm going to show you three different methods to create the logical test. And when I show you the by row and or next video in certain situations this esoteric approach is the fastest and easiest. Now the goal in this video is from this data set put the entire range into array and then somehow comma in include we need to run an and logical test. So we have a true and false in each row. So the filter function knows which rows to extract and bring off to the side. Now in this video we're going to run an and logical test. Next video we'll do an or logical test and we have 1 2 3 four different columns with four different conditions. We're going to ask the question hey product column are you equal to quad and is the region equal to east and sales rep equal to chin and customer equal to C20. And what that means is only when we get one, two, three, four TRS, Yanaki. If I change this to Yanaki, we'll get a true. East, chin, C20 equal to C20. Only when we get those four TRS, then that signals filter to take the entire record, including sales, and bring it off to the side. Now, an and logical test requires that all tests must be true. In our case, we have four different tests. they all have to come out true. Next video when we do an or logical test, we'll see that one or more tests have to come out true. For the or logical test to deliver a true. Now, I'm going to show you three different ways to build a column of trus and falses that can signal to filter that it wants a particular record to be extracted. Now the first way is really not a way that we can use directly inside of the filter function because there is an and function but guess what that's an aggregate function. It can only deliver a single answer. The direct array formula and then the amazing by row and function. Both of these can spill an entire column of trus and falses which is what we need in that allow argument. But it's worth looking at. And a lot of people think, "Yeah, why can't I use and inside a filter? " Well, here's why. Hey, I'm going to say as a relative cell reference, the product cell for this row, are you equal to that condition? And you have to hit F4 because we have to copy this down. That's logical one, comma, logical two. Region equal to that region. F4, comma, the third logical test. cell in this record are you equal to the condition F4 comma in the final logical test as a relative cell reference. a cell in this record are you equal to the condition F4 close parentheses and no matter how hard you try when you enter this controll enter you get a single true or false in our case a true not a full column of trus and falses but we can copy this down I see my crosshair angry rabbit double click and send it down but that's a formula in each cell and that will not work inside of the filter function this and can only deliver one true or false at a time. It can't deliver an entire array of trues and falses, which is what we need. Now, to show you explicitly that we can't run a dynamic spill array formula and create the full column, if I did this sort of test whole column compared to the particular condition, even though I put it into and when I controll enter, all it can do is aggregate and deliver a single answer. F2. Now, this construction is what we'll do next, but it won't be inside of the and function. When we switch over to a direct array to spill a bunch of trus and falses for an and logical test, the math operator is multiplication. For or, it's addition. Not only that, but a couple important rules. And all tests have to be true or one or more. any math operation on a true or false. Notice it doesn't matter for this math operation

or this one. Any math operation on a true or false will convert true to one and false to zero. Not only that, but any nonzero number in Excel - 20 1 all of them are true. 0 is the only number that is false. Now to create our formula for the and logical test equal sign open parentheses and we highlight the entire column. Control shift down arrow controll backspace and we ask the question hey are any of you equal to whatever the condition is in cell B15. Now close parentheses. We absolutely don't need to lock. It's not like the and formula. This will spill all of the answers. Now, if we highlight just this little bit, we can see the answer. A full column of trus and falses with the same number of rows as the data set. And that's the answer to logical question number one. Now, we're doing an and so we have more than one test. Multiplication open parentheses. Get the second column. Are you equal to that condition? Use multiplication again. Next test still has to be inside of parentheses. and then the final test customer and close parentheses. Now why do we have to use parenthesis? Because the order of operation in Excel does math operations way before comparative operator. So we have to force this first each one of these that will give us an array of trus and falses. We can highlight and see multiplication will say hey true time true times true which is really one times 1* 1 anytime there's a record that meets those four conditions the array will deliver a one so when I controll enter I spill the results one is the same as true to excel zero that means false down here 2527 sure enough we have build a one. Now, guess what? We can use this directly inside of the filter function. I'm going to edit mode, highlight, copy, escape. Come up here. Equals filter. Highlight the entire table including the sales column. Control shift down arrow. Controll backspace. There's array comma include controll close. Controll enter. And bam, there's the extracted records. If I change this to quad, sure enough, I get a different set of records. Now, actually, if you had this column there for some reason, you could equals filter, select the entire array. Control shift down arrow, controll backspace, and simply use a helper column. Controll backspace. Close. Controll enter. But we did one, two different methods. One, two. Here's the best way. And there is a caveat, right? The order of columns, product, region, sales rep, and customer are the same as we set it up for the conditions. And the operator is the same. So guess what? All we have to do is actually just for a second, I'm going to do this off to the side. I actually hid some columns, but check this out. Control backspace. I did not highlight sales. This is the logical test. I'm just going to do all four columns and ask the question, are any of you equal to? And because this is a single row with four columns, each element in this row will be matched up against the rows in the large array. If I controll enter, that's the actual result. And if I change this just so we can see a full row with TRS, there it is. True. true. Now we just need to run an in logical test row by row and F2 by row will do that. We're telling by row, hey, there's the array. It doesn't matter if it's got an equal sign and it's generating trus and falses because the function and it will run it row by row and deliver a single column of trus and falses. F2. I'm going to highlight all of this. Ctrl X to cut. Enter. I unhid the rows. F2 controlV just so we have it as a trail. But guess what? Now inside a filter, highlight the entire array. Doesn't matter how many columns as long as it has the same number of rows as whatever formula element we put into include. Ctrl +V. We'll get rid of this equal sign. Close parenthesis. Crl enter. And just like that, we've extracted the records meeting this and logical test.

If I change this to Carl, everything updates and we have the choice. Do we want to do it the longer direct array formula way or do we want to do it the shorter way with by row and and? Now, here's your bonus. Normally, when we enter a formula, of course, there's no preformatting. How did I get that formatting to change when the records expanded or contracted? Well, for the number formatting, that's easy. You always have to highlight enough records to accommodate the most possible records extracted for whatever set of conditions you have. I know we're never going to get more than that number of rows. Controll one. And for number formatting, I'm just going to add it to all the cells because when there's nothing there, we don't see it. But for the border in the green, you highlight the maximum number of cells to accommodate the condition. And we can actually use a built-in conditional formatting. Home styles conditional formatting. New rule. New formatting rule dialogue box. Format cells that contain. And there's a built-in feature. No way. No blanks. I'm going to format. I like more colors. I like this green because before I used Excel, the color of the accounting paper I used was that color. So, I always use that color for formulas. Click okay. Border outline. Okay. Bam. There it is. If I change a condition, sure enough, it's working like a charm. All right. That was a lot of fun with and logical test inside of filter using bro and a direct array operation. Or if you want to use and for some reason, you can just refer to it. All right, we'll see you next video for an orological test inside a filter.