Excel FILTER Function TRICK for Non Adjacent Columns

  

 need in your analysis. You can also use thisto return unique values from non-adjacent columns. It's one single formula that spills and gets the job donefor you in record time. (upbeat music) In our first example, we want to get a listof names and positions of employees that have a salarythat's greater than 100,000. Now we want our results right here. And we want it be ina dynamic way so that, whenever we update this number, our results will automatically update. Our raw data is right here. And, we have some additionalinformation than what we need. So we also have information on department, start date and birth date. Now just for simplicity, I put the data set beside where we're going to do the analysis. But of course, the raw data can be on a separate tab as well. Now my data here goesall the way to line 68. What I'm going to do is to turn this into an official Excel table, so that whenever I add new lines to this, my results will also automatically update. So let's do that first, just go somewhere inside thedata set, and press Ctrl T, my table has headers. Now, I'm just going totake away the formatting and update the name toTsal, for table salary. Now what I want to do here is to use a formula becauseI want this to be automatic. I don't want to have topress refresh or anything, I want my results to updatethe moment something changes. This means that I'm going to use the new Microsoft 365 filter function for Excel. Now if you're not familiarwith the filter function, I have a separate video on that, I'm going to add the link tothe description of this video. So check that out. In this lecture, we're going to use a trick with that function so that we can returnnon-adjacent columns. Let's start off by using the traditional filter function first. The first argument is the array. This is the columnsthat you want returned. What I want returned hereis name and position. But check this out. Name and position aren'tbeside one another. And for array, I can just have one range. So I cannot select them individually. What I have to do isselect the entire range and also include start date and birth date in there even though I don'twant them in my end result. But we're going to takecare of those in a second, so stay with me. The next argument is the include argument. I want to include only two rows where I have a salary number that's greater than this number. So I'm going to highlight salary and greater than this one right here. If you also wanted it to begreater than or equal to, you can add an equal sign here. In this case, I just want to go with a greater than. Last argument in filteris an optional argument. What do you want returned ifthere are no matches found. So if no one is earning more than 100,000. Well, in this case, I don't need to worry about that because I know I'm going to have a match. So I'm just going to close bracket, press enter, and I get afiltered view of my results that's dynamics. So I get Wolfgangbecause he earns 116,000. Then I get Paul. These ones are not in my results. And then I get Stevie Bridge right here. I notice also, that Iget these two columns. I see them as numbers because they're not formatted as dates. But these are basically mystart dates and birth date, which I actually don't wantbecause I just want position. So here's what you needto do to filter out what you don't want. Check this out, we're going touse another filter function. The array is basicallythis, what we see here. Now what do we want included? Well, for this include argument, I'm going to use array constance. So I'm going to open the curly bracket. And I'm going to flag the columns that I want to keep byusing ones and zeros. So check this out. In the original array, Iselected four columns, right? Now we're going to saywhat we want to keep, I want to keep the name and I'm going to put a one comma, I don't want to keep startdate and birth dates. I'm going to put a zero comma zero comma. I want to keep the lastone, I'm going to put a one and I'm going to close the curly bracket. So in a way, what I'm doing here is I'm broadcasting theone to devalue sitting in this column. What the include argument or filter does, is it includes values that are true, and four Excel, ones aretrue and zeros are false. So this is a way that you canflag what you want to keep. Now check this out, I'mgoing to close the bracket and press enter. And I only get the columnsthat I want to keep. Now I first saw thisfrom one of my students, Adam, who's in my dynamic array course. And I loved his solution. So I had to share it withyou because it's so simple, and it's really easy to remember. Just flag the columnsthat you want to keep. Now in case you want to get this sorted. You can continue and wrap thisinside the sorted function. By default, it's going to sortbased on the first column. So I'm going to close bracket press enter. I have everything sorted by name. If you wanted to sort by position, you can add the sourceindex argument here, which is the second argument. So I'm going to put a comma here, and sort by the second column. So that's going to sort by position. Now you can use this trickfor other things as well. So you could use it to get a unique list for department and position. So basically a unique listof non-adjacent columns. Now let me quickly show youI'm just going to hide this. So we're closer to our data set. The unique function is alsoone of the new functions that was introduced in 365. It basically returns adistinct lists of values. So if I wanted just adistinct list for department, I can select department here, close bracket and press enter. Now in the same way, youcan get a unique list of more than one column, but they have to be beside one another. So I could return a uniquelist of department and name. In this case if they'rebeside one another, it's no problem. But that's not what I want. I want to get a unique list for department and position right here. The problem is they arein separate columns. Here, I'm going to usethe filter trick as well. First, what I'm going todo before using unique is to filter out the columns that I want from my original list. So I'm going to highlightthe entire array. And then in the include argument, I'm going to flag thecolumns that I want to keep. So I want to keep the firstone that's a one comma, then 000 for name, startdate and birth date. And last one is for position I want. So I'm going to put aone, close curly bracket, close bracket and enter. So now I have department and position. Then I can put thisinside my unique function, and I can get a unique listfor department and position. Now if you want to get it sorted, you can put this insidethe sort function and sort. In this case, we're going tosort by department, right? So a very simple flagging solution to only include the columnsthat you want returned. Now, remember, I said we wanteverything to be dynamic. So let's just test for that. Let's unhide this. I'm going to change this to 120,000. And my results update automatically. Now let's also make sure thatif we add new lines to this, everything updates as well. So I'm going to add anew legal department, and let's just add a consultant for 50,000. Now, we shouldn't see thisin our first list here, but we should see it here. We see the legal department. And if this person gets a raise, and they earn 130,000, we should see Lilli right here. As a bonus tip, let'ssee how we could sort these based on their salary. Here, what I would do, is first just filteredinformation that I need. So let's start out with filter. My array is basicallywhat name and position but I'm going to include salaryin the first round as well, just so that it's easierfor me to sort it, and then I'm going to excludeit from my final result. So that's my array. Next, what I want toinclude is just like before, I want everything included, that is greater than this number here. Right, so that's my filtered result. Now everything is going to spill and it's not going to have enough space because I have these in front. So let me just insert some lines. Just so that we can see our numbers here. And anyhow, we're goingto be restricting it to two columns in a second. It's just easier to write the formula. If you start from theinside and then go out. Next is to get this sorted by salary. So I'm going to sort, my array is this and I want to sortedit by the last columns, that's 1, 2, 3, 4, 5. And I want it in descending order. So I'm going to put a minus one. Close bracket, press enter. Everything is sorted by salary. So Crystal Doyle is firstand Stevie Bridge is last. Now, I only want to include it, the name and the position. So let's put this nowinside our filter trick. That's my array. My include argument are myflags of what I want included. That's one, then zero for start date, zero for birth date, one forposition and zero for salary, because I don't want toshow that in my end result, close bracket, press enter. I have everything sorted indescending order by salary. That's the filter trick you can use to return values basedon non adjacent columns. I love the simplicity of this. If you're also a fan of this solution, do give this video a thumbs up. Now I have a complete courseand the new Excel functions, where I show you how you can maximize the potential of the new Excel. Check it out if you haveExcel for Microsoft 365, link to it is below. Now before you leave, don't forget to subscribe soyou can join our community here and stay up to date withnew functionalities. I'm going to see you in the next video. (upbeat music)