Excel FILTER Function TRICK for Non Adjacent Columns

  Рет қаралды 341,396

Leila Gharani

Leila Gharani

Күн бұрын

Пікірлер: 757
@LeilaGharani
@LeilaGharani 10 ай бұрын
Grab the file I used in the video from here 👉 pages.xelplus.com/filter-trick-file
@bobgreenfield9158
@bobgreenfield9158 5 ай бұрын
I tried Ctrl + T and nothing happened. You already have the table set.
@ChaplainRMCSJ
@ChaplainRMCSJ 3 ай бұрын
I already have the file, thank you.
@rogerhendriks999
@rogerhendriks999 4 жыл бұрын
Thanks, Leila, for yet another very helpful video. Just for those who live in a country like me, where you have to use other separators: to make this trick work, instead of the "," you'll have to use the "\". The first formula then looks like this: =SORT(FILTER(FILTER(TSal[[Name]:[Position]];TSal[Salary]>J2);{1\0\0\1});2) And then it works miracles. Had been looking for this trick for so long, glad I know it now!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks for sharing, Roger!
@meaakv
@meaakv 3 жыл бұрын
Thanks, Roger! Had the same issue.
@PS-gn4xg
@PS-gn4xg 2 жыл бұрын
Thanks Roger!
@alexpedrosantos
@alexpedrosantos 2 жыл бұрын
Thanks a lot Roger. I was trying everything as a separator and none worked until seeing yours! Portuguese excel.
@shorthey
@shorthey 2 жыл бұрын
Not typically one for commenting youtube videos, but I've been looking for this for a couple of hours now and it worked perfectly. Thank you Roger! And big ups to Leila as always. Confirming forward slash separators work instead of comma for Norwegian excel users.
@bfilgate
@bfilgate Жыл бұрын
I googled and could not find anything on how to do what described in my comment below - but then I was messing with the FILTER function and realized I could put the HSTACK function inside the first parameter of the FILTER function and that gives me exactly what I need. i.e. to select the specific columns by column name in a FILTER function in any order without choosing all of them, etc. and without relying on the column order in the source table. It works great! Thought it might be worth a video....
@bday9629
@bday9629 4 жыл бұрын
I want to express how much I love you Leila, I started to need to learn excel about 2 years ago, and whenever I am stuck I searched up it is always your videos that knows what I am looking for, thank you!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
You're so welcome!
@pradhanbalter3796
@pradhanbalter3796 3 жыл бұрын
I love Leila and everything she does. Her videos are so clear, step-by-step and covers every different "what if?". When it comes to Excel, her channel is always the first place I look.
@patrickdolisie7037
@patrickdolisie7037 2 жыл бұрын
I really think you are a genius Leila. And the opportunity to download your file is great. It allows to understand bugs between English version of Excel et French one for example. In that case {1,0,0,1} becomes {1.0.0.1} in the French version. Once again, I, like millions of people, really appreciate what you do. Thanks !
@bilalyenimol3502
@bilalyenimol3502 2 жыл бұрын
Really thanks for this comments i was trying to figure out where i am making mistake thanks bro :D
@davidabuang
@davidabuang Жыл бұрын
The second FILTER trick is cool, but I think some people are struggling to understand how it actually works, because filtering is typically only applied to rows. However, the FILTER function is capable of filtering data both vertically and horizontally, which is the case in this example. To better illustrate how a horizontal filter works on columns, write some OR criteria for the header row like this: =FILTER(TSal,(TSal[#Headers]=I4)+(TSal[#Headers]=J4)) So, to achieve the same results as demonstrated in this video, the final nested formula would be: =FILTER(FILTER(TSal ,TSal[Salary]>J2), (TSal[#Headers]=I4)+(TSal[#Headers]=J4)) Sure, the array constant method is shorter in this example, but the horizontal criteria method has other advantages: 1) it will work regardless of the column delimiter used in your region 2) it will continue to work if new columns are added/inserted 3) it’s easier to manage with larger tables (20+ columns) Cheers!
@theawebster1505
@theawebster1505 Жыл бұрын
That's definitely a more sound solution, @davidabuang The video is 100% great anyways!
@lucmorineau6741
@lucmorineau6741 4 жыл бұрын
excellent, thank you. Just a note for those who will use "European configuration"The formula is becoming : "=FILTER(SORT(FILTER(TSal[[Name]:[Salary]];TSal[Salary]>J2);5;-1);{1\0\0\1\0}) "
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks Luc!
@sahilsinghal9472
@sahilsinghal9472 4 жыл бұрын
I had been breaking my head for the last few days on this exact problem. Your solution is brilliant. Makes the filter function so much more useful. Thank you so much Leila
@thefotoshooter
@thefotoshooter 4 жыл бұрын
Watched the video a half hour ago, and already used it to save about 15 minutes of manual effort. Great function and GREAT instruction on using it.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Great to hear it was helpful, Andy!
@favreje
@favreje 4 жыл бұрын
What a great solution! I tweaked the formula to replace the implicit constant array with a simple if() statement so the user can flag with a "y" (yes) above each column that they want to keep: =FILTER(FILTER(Tsal[[Name]:[Position]],Tsal[Salary]>J2),IF(B1:E1="y",1,0)). This way non-power users can dynamically choose the columns in their report. Thanks again for the simple solution to this issue - I looked everywhere for one!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks for sharing your version, Jeff!
@MDNaHas_Mahmoud
@MDNaHas_Mahmoud Ай бұрын
thank you very much
@dokudowiec01
@dokudowiec01 2 жыл бұрын
This is my favourite Excel function so far. I use it every day. The only thing that I hate about it, is that I had to redo all my sheets ;) I love that you can use as many 'include' arguments as you want, like an 'IF' function. Just put all 'include' arguments and '*' between them. Genius! - Leila, great work! Please keep it up.
@sharon4578
@sharon4578 3 жыл бұрын
Till now I use compicated formulas or pivot to do all of it , now the life changed with this amazing function Many thanks Lili
@LeilaGharani
@LeilaGharani 3 жыл бұрын
Great to hear!
@a.achirou6547
@a.achirou6547 Жыл бұрын
Waou ! I love the simplicity of the filter trick. Thank you, Leila, for sharing this. It is a good alternative to CHOOSECOLS function for filtering the output.
@TheZofriax
@TheZofriax 4 ай бұрын
You the real MVP!
@marktheirl1842
@marktheirl1842 3 жыл бұрын
Leila, I have been a subscriber for a long time now and I am still amazed on how much I can learn from you!!!
@LeilaGharani
@LeilaGharani 3 жыл бұрын
I'm so glad! Thank you for your support, Mark.
@JaskellK
@JaskellK 3 жыл бұрын
Our IT department finally updated our 365 add-ons. Sad to say i work for a Tech company, but been waiting to use FILTER function. This video helped me trickle down to the columns I needed which is about 5 from about 30 columns. Thank you!
@LeilaGharani
@LeilaGharani 3 жыл бұрын
Great! Glad it was helpful.
@dennisd5776
@dennisd5776 4 жыл бұрын
What a simple solution to a complex problem using only a single formula. Thank you for the tip.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
You're welcome, Dennis!
@samruben5750
@samruben5750 2 жыл бұрын
That was awesome! For anyone getting a #VALUE! error - 1. Check that you're using Excel 2021 or 365 2. Make sure that the array constant of boolean values you use for the include argument has as many values as columns present in the array argument! So if I'm filtering an array from Department to Salary, the curly bracket array constant must have five values (Department, Name, Start Date, Birth Date, Position).
@SteveSwanson999
@SteveSwanson999 2 жыл бұрын
THANKS SAM! The second part of your comments (having as many values as in the array) saved me TONS of debugging...GOOD YOU
@samruben5750
@samruben5750 2 жыл бұрын
@@SteveSwanson999 Glad I could help! I wasted a good chunk of time trying to figure it out myself 😂
@johnborg5419
@johnborg5419 4 жыл бұрын
Amazing Leila. Never thought of that in a million years.
@mdtechpk739
@mdtechpk739 4 жыл бұрын
You have made it super easy. Thumps up for teaching us extraordinary tricks. Thank you
@mcpett5742
@mcpett5742 Жыл бұрын
Can't believe there was this solution, I remember brain storming for half hour and I finally went with the choose function
@walkwithmedownunder8089
@walkwithmedownunder8089 3 жыл бұрын
I have been working on a solution for this for who knows how long and you solved it in minutes! Thanks you so so so very much Leila!
@RRexxxxxxx
@RRexxxxxxx 4 жыл бұрын
Dear Leila, I'm not sure I'm gonna use this tips (I'm a very basic excel user), but I just can't stop watching your videos. You are great!!! Love how you explain things!
@ExcelUnlocked
@ExcelUnlocked 4 жыл бұрын
Hello, trust me, you keep watching, you won't be a basic user for long 😉
@danielmpinga4102
@danielmpinga4102 8 ай бұрын
3 years later and here I am using this solution. Thanks for all these helpful videos over the years.
@LeilaGharani
@LeilaGharani 8 ай бұрын
It's our pleasure. Thanks for your ongoing support!
@arkadiuszstojek9713
@arkadiuszstojek9713 4 жыл бұрын
Awesome function, awesome video :) Also it will well pair up with choose function. FILTER(CHOOSE({1,2},TSal[[Name],[Position]]),TSal[Salary]>J2)
@levi822
@levi822 3 жыл бұрын
very nice
@dirkstaszak4838
@dirkstaszak4838 2 жыл бұрын
in my Excel this is not working. The formular should be like this: FILTER(CHOOSE({1,2},TSal[Name];TSal[Position]),TSal[Salary]>J2) In addition I noticed that in build 2108 14326.20784 in the German version WAHL({1.2}...) the choose part must be spearate by "." to achieve the same. In the beta release channel it changed to "\" for the same result. Apparently Choose acts differently in country versions as well as in build version.
@krishnamurthy7733
@krishnamurthy7733 6 ай бұрын
It's nice and easy step to filter the Non-adjacent columns. Before watching this video, I had been using the HSTACK function to create an array from Non-adjacent columns.
@barttitulaerexcelbart9400
@barttitulaerexcelbart9400 4 жыл бұрын
Thank you Leila, I think you were the first who makes a video about his. Finally a good alternative to advanced filter...
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Without Adam's idea I wouldn't have come up with it either. It's a great alternative!
@gunaybabacanova
@gunaybabacanova 8 ай бұрын
Have a teacher like you,its a big chance.You are amazing .Yours youtube videos give me more than my teachers .THANK YOU ❤
@mikedodds1321
@mikedodds1321 2 жыл бұрын
I really need to just come to your videos before I start any task. You always save me so much time!
@saptharishi1974
@saptharishi1974 4 жыл бұрын
I have become your ardent fan - absolutely brilliant explanation!!! Most importantly the pace that you eloquently navigate thru, thanks a lot!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Awesome, thank you!
@Fahad-AlGhamdi
@Fahad-AlGhamdi 4 жыл бұрын
Greetings from🇸🇦 Saudi Arabia 🇸🇦 . Your channel is wonderful in explaining Excel. I wish you more excellence
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks a lot!
@mmjjagon5419
@mmjjagon5419 3 жыл бұрын
Really excellent. Again a more than useful video. Thank you Just to help : for French version of Excel, we have to write {1.0.0.1} instead of {1;0;0;1}
@erwanlecocq381
@erwanlecocq381 2 жыл бұрын
MERCI!!!!! Une heure que je me debat avec excel sans rien n'y comprendre! Merci!
@jamalkarimi8860
@jamalkarimi8860 Жыл бұрын
For German version dots are also the delimeter
@sbfredster
@sbfredster 2 жыл бұрын
Boom, solved the annoying problem of having to hide columns! Thanks Leila! I ran into another annoying result where the filter function returns zeros for blank cells in the source data. I found an elegant solution on Mr. Excel that may help others...made sense to me as I just watched Leila's video on the LET function ;) ... to remove zeros in the array returned by filter try a version of the following example: =LET(f,FILTER($A$1:$D$21,$B$1:$B$21="your_filter_criteria"),IF(f=0,"",f))
@LeilaGharani
@LeilaGharani 2 жыл бұрын
Thanks for sharing your solution!
@xjustinx1993
@xjustinx1993 9 ай бұрын
Or just go into file-options-advanced and deselect the option that says “show 0 for blank cells. “
@ekhp91
@ekhp91 4 жыл бұрын
I've been combining it with the indirect formula and making separate formulas for each column that I need. This really helps!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Excellent!
@RZA12
@RZA12 9 ай бұрын
You're a great teacher and I'm glad to find your channel. You teach in a brilliant way and I completely understand. Thank you very much 🙏🏼
@LeilaGharani
@LeilaGharani 9 ай бұрын
Thank you very much!
@Galileo2pi
@Galileo2pi Жыл бұрын
Leila, she's the better in this land, this is my opinion. She opened my brain, and my life changed; she's a good professional. Do not forget her team, excellent as well.
@LeilaGharani
@LeilaGharani Жыл бұрын
Thank you so much for the kind words!
@ShaikPashaImran
@ShaikPashaImran 4 жыл бұрын
Really love the way you put up examples and explain them so easily. Thank you leila, this is the first time ever i am fan of someone who has been training online
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Glad you like them! Thank you so much for your support.
@tammystucki3152
@tammystucki3152 2 жыл бұрын
Thank you for sharing and thank your student for thinking outside of the box!
@cgrablew
@cgrablew 4 жыл бұрын
Thanks ... I did something like this a month ago but using choose and it was a lot more cumbersome..This is great easy way to do non-adjacent columns. Thanks so much!
@applezhe19
@applezhe19 3 жыл бұрын
Thanks for teaching me on filter function as well as for Non Adjacent columns, this is very helpful for me. Keep it up!
@gintomino4136
@gintomino4136 4 жыл бұрын
She always make it easy for us to understand. I wish all teachers are like you.
@ronaldarvin414
@ronaldarvin414 3 жыл бұрын
This is what I exactly need right now. Thank you! Subscribed.
@derekpross4914
@derekpross4914 3 жыл бұрын
I'm late to this video. I needed to build unique lists from large data sets for sorting and SUMPRODUCT and SUMIFS analyses. This worked like a charm. Thank you!
@LeilaGharani
@LeilaGharani 3 жыл бұрын
Glad it was helpful!
@SimonLangridge-no5gu
@SimonLangridge-no5gu 7 ай бұрын
Found this tutorial, was exactly what i was looking for. Was lost now found. Thank you so much.
@LeilaGharani
@LeilaGharani 7 ай бұрын
Glad it helped!
@michaeltedford2177
@michaeltedford2177 4 жыл бұрын
Leila, Once again you display a fantastic option to extend the usefulness of Excel. It makes keeping data updated very easy. Thank you. One curiosity I found is, if you drag the formula, like a normal copy, the formula changes to reflect each column in the table. Example - =FILTER(FILTER(Table2,Table2[Subsegment]=D1),{0,1}) turns to =FILTER(FILTER(Table2,Table2[LEGACY]=E1),{0,1}). It is easily solved with a regular copy and paste. Thank you again.
@rperea73
@rperea73 Жыл бұрын
Thank you, Leila! Was racking my brain on how to solve this till I luckily found your video. :D Greetings from Panamá.
@MrYogiAsliwale
@MrYogiAsliwale 4 жыл бұрын
i was using the filter function... but didn't realize it was array formula and hence could filter multiple columns and in this way!! Thank You Leila. and as usual... i love the way you say "Eeks" for X :D
@LeilaGharani
@LeilaGharani 4 жыл бұрын
FILTER really is an amazing function :)
@EricHartwigExcelConsulting
@EricHartwigExcelConsulting 3 жыл бұрын
This video is so awesome! I have been looking for a way to extract specific columns from within a filtered data set and now thanks to this video I know how to do that!
@LeilaGharani
@LeilaGharani 3 жыл бұрын
Glad it helped, Eric!
@mikeszczesny4117
@mikeszczesny4117 7 ай бұрын
You can just use one filter function to select name column and position column, then add your filter criterion. =filter(choose({1,2}),name range, position range),salary range>=criteria). With the choose function, you can select any column and how many columns.
@asam
@asam 4 жыл бұрын
You have really upped the quality of the videoes! Love the editing, the small details, music, transition etc. Awesome! :-)
@mdtechpk739
@mdtechpk739 4 жыл бұрын
Agreed
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks a ton! I'm glad to hear that 😊
@muhammedmm1407
@muhammedmm1407 4 жыл бұрын
Leila Gharani thank you, I have one more query about =Filter formula is possible to do Based on Mutiple conditions I mean by list of drop down shouldn't in information. I hope you are going to do one video about this
@HusseinKorish
@HusseinKorish 4 жыл бұрын
That's Amazing Liela ... i used to go around this problem for months ....now you solve it ...Many Thanks
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Happy to help!
@mattschoular8844
@mattschoular8844 4 жыл бұрын
Wow! I have been dabbling with the new functions for awhile now. This video is packed with great content. Thanks Leila and Adam for sharing your knowledge.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Our pleasure, Matt :)
@SaravSood
@SaravSood 3 жыл бұрын
I simply love your teaching method & style, learner lot of things in simplest way.. you rock.. thank you so much
@67duiker
@67duiker 4 жыл бұрын
I would use =SORT(FILTER(CHOOSE({1\2};TSal[Name];TSal[Position]);TSal[Salary]>J2)) as this gives more visibility in the formula which columns are chosen. But Your trick has the advantage that it is easier to change between columns when you need to. I changed the {1,0,0,1} to a range and could then choose which columns i wanted to see. Very useful trick
@67duiker
@67duiker 4 жыл бұрын
I just tried the following formula CHOOSE(+TRANSPOSE(SORTBY(FILTER(T6:T10;S6:S10>0);FILTER(S6:S10;S6:S10>0)));TSal[Name];TSal[Start Date];TSal[Birth Date];TSal[Position];TSal[Salary]) where I put in r6 to r10 the names of the columns and in 26 to s10 the columns i wanted with 0 to 5 to indicate where i wanted them(0 not shown). as help-column in t6 to t10 (1,2,3,4,5) and now I can choose which columns i want in which order. Adding a filter is easy at that point.. and with a formula for the headers +TRANSPOSE(SORTBY(FILTER(R6:R10;S6:S10>0);FILTER(S6:S10;S6:S10>0))) it's a complete table. I like playing with your examples :-)
@jimfitch
@jimfitch 4 жыл бұрын
Fabulous tip! This is a great solution to several situations that I solved with far more complex DA formulas. This is sooo much simpler. Thank you!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
My pleasure, Jim! Glad it's helpful for you.
@McKaySavage
@McKaySavage 4 жыл бұрын
Really awesome. I’ve needed this trick for a while and thought it not possible. Thanks so much for sharing this! Your videos are always great, but I’m super grateful for this one!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
You're very welcome!
@AP2922
@AP2922 4 жыл бұрын
@leila I'm happy you liked that trick! But you took it and taught me new things you could do with it that I hadn't considered. When I saw the choose function method you teach for this type of problem I was sure that was the better way but your Bonus Tip example shows why we can't rely on just one formula. Both formulas have their place.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks Adam for sharing this great idea! I'm sure it will be helpful for many of us.
@a101525
@a101525 4 жыл бұрын
Wow! Great tip Leila! Another day I had really bad times trying something that would give me this same result. Thank you.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Glad it was helpful, Angelo!
@hoserbob
@hoserbob 2 жыл бұрын
Wow. Thank you. I was wondering if this could be done. This is an elegant solution.
@lmajiedmary
@lmajiedmary 2 жыл бұрын
Just what the doctor ordered. Thank you!
@johnvodopija
@johnvodopija 2 жыл бұрын
This is so powerful. Thank you for sharing and explaining it so clearly. Cheers
@LeilaGharani
@LeilaGharani 2 жыл бұрын
You are so welcome.
@LearnExcelVBA
@LearnExcelVBA 4 жыл бұрын
Thanks Leila for sharing this. This will be surely useful for 365 users.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks, I hope so!
@remars-xcel
@remars-xcel 4 жыл бұрын
You never fail to fascinate us , take care Queen 👸🏼
@david.miskick
@david.miskick 2 жыл бұрын
For those who use comma as decimal, you can write 'include part' like this {TRUE\FALSE\TRUE\FALSE etc. }
@seanbtw
@seanbtw 2 жыл бұрын
Thank you so much for this, I've been searching for hours why it didn't work for me!
@slabitgratuit
@slabitgratuit Жыл бұрын
Good job, brother! Tks.
@gloystar
@gloystar 10 ай бұрын
Nowadays I just use the CHOOSECOLS function instead, but both solutions are fine. Great video!
@LeilaGharani
@LeilaGharani 10 ай бұрын
👍
@amy19355
@amy19355 4 жыл бұрын
That is a fabulous little trick, and I'm already putting it to work. Thank you!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Wonderful!
@preeteshdoshi4035
@preeteshdoshi4035 2 жыл бұрын
Great laila ,love u , u give me more then i imagine. i identified my problem with my excel data and solution is just from you.
@nonoobott8602
@nonoobott8602 4 жыл бұрын
This is sure an amazing solution. However, another way one could filter 2 or more non-adjacent columns is using a combination of the CHOOSE and SEQUENCE function. For this example, we could've filtered the name and position earning more than 120k thus: =FILTER(CHOOSE(SEQUENCE(,2),TSal2[Name],TSal2[Position]),TSal2[Salary]>J2) OR =FILTER(CHOOSE({1,2},TSal2[Name],TSal2[Position]),TSal2[Salary]>J2)
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks Nono Obott. I like the Choose version too. It's the version I cover in the course. An alternative is to also use INDEX with Sequence. So great to have so many versions 😀
@nonoobott8602
@nonoobott8602 4 жыл бұрын
@@LeilaGharani Thank you Leila. I've learned so much from you. You have no idea. I'll l definitely subscribe for your advanced courses. Thanks for sharing from the wealth of knowledge you've got on Excel, you're phenomenal
@srvithal38
@srvithal38 2 жыл бұрын
You are just fabulous. Thanks for sharing such valuable knowledge in such a simple way. Amazing
@LeilaGharani
@LeilaGharani 2 жыл бұрын
My pleasure 😊
@keithdutch5295
@keithdutch5295 2 жыл бұрын
Exactly what I was looking for. Thanks Leila!
@navirock3575
@navirock3575 2 жыл бұрын
Thanks
@LeilaGharani
@LeilaGharani 2 жыл бұрын
Thank you!
@dashrathpanchal8393
@dashrathpanchal8393 4 жыл бұрын
Great Leila .. Literally you are dynamic like the formulas ..Super fast solution for dashboard ..A Big Thumps Up ..Thanks for sharing
@LeilaGharani
@LeilaGharani 4 жыл бұрын
You are very welcome 😊
@JosephMcDaid
@JosephMcDaid 4 жыл бұрын
Great video. Very clever use of the double FILTER!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks Joe for bringing these functions to us :)
@JOSE-du7mu
@JOSE-du7mu 4 ай бұрын
Different ways of doing the same thing; for example, she also showed us howt to do the CHOOSECOLS, to select only the column you want; I think CHOOSECOLS is better.
@wayneedmondson1065
@wayneedmondson1065 4 жыл бұрын
Hi Leila.. great trick.. FILTER FILTER with a list of array constants to extract the desired columns.. nice! This solution works great if your data table is static in term of columns. But, if you insert columns in the data table in the future, the array constants within the curly brackets cannot be made to be dynamic and so the formulas would then break until or unless modified. To avoid this problem and maintain maximum flexibility for future design changes, I like to use CHOOSE to assemble the desired non-contiguous columns, as follows: Your example cell I5: =SORT(FILTER(CHOOSE({1,2},TSal2[Name],TSal2[Position]),TSal2[Salary]>J2),2) Your example cell O5: =SORT(UNIQUE(CHOOSE({1,2},TSal2[Department],TSal2[Position]))) Your example cell I13: =CHOOSE({1,2},INDEX(SORT(FILTER(CHOOSE({1,2,3},TSal2[Name],TSal2[Position],TSal2[Salary]),TSal2[Salary]>J2),3,-1),,1),INDEX(SORT(FILTER(CHOOSE({1,2,3},TSal2[Name],TSal2[Position],TSal2[Salary]),TSal2[Salary]>J2),3,-1),,2)) Granted, the formula in cell I13 may be a bit more complex than FILTER FILTER, instead using CHOOSE to assemble the 3 columns to sort and then INDEX to extract the 2 columns to present, but I always thank myself for building in the added flexibility for future design changes. My formulas above will all withstand column insertions in the data table without the need of modification. The FILTER FILTER with {} list formulas would require rebuilding. Again.. not better or worse.. just different potential, depending on whether or not there is a chance the underlying data table might change in the future. A good parallel is VLOOKUP vs. either XLOOKUP or INDEX/MATCH. VLOOKUP is less flexible to future changes, but perfectly functional if you know the data table design will remain static. As always, thanks for the video and the inspiration to create and share. Always good learning and fun at your channel :)) Thumbs up!!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks Wayne for sharing. Yes that’s correct. Choose function is great - it’s the one I teach in the DA course for these cases. The good thing about choose aside from being flexible with new columns is that you can get columns in the opposite order as well. Another alternative is to use Index and sequence. Many thanks for your feedback 😊
@wayneedmondson1065
@wayneedmondson1065 4 жыл бұрын
@@LeilaGharani Thanks Leila.. I intended to mention that CHOOSE also gives flexibility of column order.. glad we are on the same page. I really appreciate all your videos and courses. Though my work and schedule get in the way of bearing down like a full time student, I consume new content every day and it adds up over time. Keep up the great work :)) Cheers!!
@PabitraJanajana
@PabitraJanajana 4 жыл бұрын
Hi! Leila, you doing very great and I am very thankful for what you are doing. I am very helpful with your videos. Now I am requesting you to help me on that my data has multiple line of a product with different quantity for a day. How I use filter function with some other formula to get filter data with sum of data. It's very challenging to me. Please help.
@mcd22630
@mcd22630 4 жыл бұрын
What a great approach on such a common task. Also congratulations on breaking through the 500k subscriber milestone.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thank you so much 😀
@aguerojg
@aguerojg 4 жыл бұрын
What an amazing tutorial, you explain so well and step by step, thank you very much Leila! The subtitles are so useful to fully understand, as English is not my native language. Thanks a lot for all your effort :)
@LeilaGharani
@LeilaGharani 4 жыл бұрын
You're very welcome, Juan 😊
@anthonyverdin6743
@anthonyverdin6743 3 жыл бұрын
OMG.....Thank you so very much. You save my day Leila!!!
@AdamKaraszewski-b4x
@AdamKaraszewski-b4x 2 ай бұрын
Thanks a lot for the mask {1;0;1;1;...} tip! Adapted it to "Chose arbitrary columns with arbitrary order, by their names". More readable. I avoid 'abstract' numerical masks with tables, so there is an example with named column mask, and you set cols order, too :) Example - I needed to select ordered unique records with my own column order and selection, after filtering them : =SORT(UNIQUE(""&CHOOSECOLS( FILTER( myTab; (myTab[myColX]=myTab[myColY]+myTab[myColZ])*(myColA=0) ); MATCH({"myColX"; "myColZ"; "myColY"; "myColA"}; myTab[#Headers]; 0));FALSE;FALSE) ;4)
@sharadpunita
@sharadpunita Жыл бұрын
So nice of you to teach such a great IDEA. Thanks
@LeilaGharani
@LeilaGharani Жыл бұрын
You are so welcome!
@kulbhushangaur6298
@kulbhushangaur6298 2 жыл бұрын
You are awesome, I got stuck to add two columns in filter function…thank you very much
@ugabrew
@ugabrew 10 ай бұрын
This was awesome, and such a simple and elegant solution. Thank you for another great lesson.
@paulsheffield3042
@paulsheffield3042 4 жыл бұрын
HI Leila, absolutely loving your courses and tutorials...thank you. Can UNIQUE and FILTER be used in a table by any chance? I get a SPILL# error. Also, can you use SUM function with UNIQUE and FILTER to aggregate column values of all of the lines related to the unique record. Fingers crossed...thanks again.
@izkamarkizka
@izkamarkizka 2 жыл бұрын
Thank you, Leila. It is always such a clear explanation.
@LeilaGharani
@LeilaGharani 2 жыл бұрын
Glad you think so!
@Sarunsherief
@Sarunsherief 4 жыл бұрын
As usual another cool trick 😎👆 Thank You Leila Looking forward to the next session.
@GhostVale4698
@GhostVale4698 Жыл бұрын
I can't thank you enough for this video. Thank you so much and keep up the good work
@LeilaGharani
@LeilaGharani Жыл бұрын
You're very welcome!
@garyallan69
@garyallan69 2 жыл бұрын
Love it. I've wanting to know how to use this for a long time. Had to resort to using Xlookup before now for the second column
@nonoobott8602
@nonoobott8602 4 жыл бұрын
This is so amazing and very useful. You explanation makes it so easy to grasp. Thanks for sharing
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Glad it was helpful!
@operamaniak81
@operamaniak81 2 жыл бұрын
Mind blown! I've immediately used it!
@LeilaGharani
@LeilaGharani 2 жыл бұрын
Happy to help!
@garys2187
@garys2187 4 жыл бұрын
That is incredible!!Thank you for sharing. I am in the process of taking your dynamic array course, which I am enjoying very much - a very worthwhile investment. I am well past the Filter section of the course, so I am making a note to myself that this trick exists.
@pupycron4302
@pupycron4302 3 жыл бұрын
Fabulous and very simply explained. This is great.
@niteshmech1
@niteshmech1 Жыл бұрын
Thanks Leila 👍Your trick helped me lots in my desgn calculations.
@math2693
@math2693 4 жыл бұрын
This video has made my life easier
@JustAbackYardCook
@JustAbackYardCook 4 жыл бұрын
All of your videos are excellent! My favorite for learning excel!
@SaravSood
@SaravSood 3 жыл бұрын
mine too...
@1nannapaneni1
@1nannapaneni1 4 жыл бұрын
Thank you Leila. Wonderful presentation. Very clear understandable easily every having basic knowledge. Keep it up.
@mohitjagad
@mohitjagad 4 жыл бұрын
Hi Leila, Thanks for sharing this amazing trick . 😊👍 I believe this will ease the work on large tables / data sets. Thanks 😊⭐
@LeilaGharani
@LeilaGharani 4 жыл бұрын
You're welcome 😊
@dennishd848
@dennishd848 4 жыл бұрын
Awesome, my boss was asking me this yesterday, today I got answer. Thank you so much
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Great timing!
@bravucod
@bravucod 4 жыл бұрын
I was looking for this solution the other day. Thanks for the TIP!
@BitesizeExcel
@BitesizeExcel 4 жыл бұрын
This is a great video - love the tip and the way you've broken down the elements! I've been having a play around with the new functions myself and love the useful functionality they bring! :)
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Awesome! Thank you!
@alexpedrosantos
@alexpedrosantos 2 жыл бұрын
Absolutely fantastic. Thank you for teaching.
@LeilaGharani
@LeilaGharani 2 жыл бұрын
You're very welcome!
@monte4955
@monte4955 4 жыл бұрын
Great as always Leila. Will definately be using this. Good to see your channel growing, over 500k subscribers 👌
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks a million!
When You Should Use the New Excel LET Function
17:18
Leila Gharani
Рет қаралды 773 М.
When Should You Use the Hash Sign in Excel Formulas?
10:53
Leila Gharani
Рет қаралды 1,1 МЛН
小路飞和小丑也太帅了#家庭#搞笑 #funny #小丑 #cosplay
00:13
家庭搞笑日记
Рет қаралды 17 МЛН
How Many Balloons To Make A Store Fly?
00:22
MrBeast
Рет қаралды 189 МЛН
Tuna 🍣 ​⁠@patrickzeinali ​⁠@ChefRush
00:48
albert_cancook
Рет қаралды 104 МЛН
The Excel Tool That Does What PivotTables Can’t (File Included)
13:59
MyOnlineTrainingHub
Рет қаралды 111 М.
Next level FILTER Function tricks | Excel Off The Grid
13:23
Excel Off The Grid
Рет қаралды 33 М.
Excel RECURSIVE Lambda - Create loops with ZERO coding!
12:51
Leila Gharani
Рет қаралды 160 М.
Try The DGET Function Instead of INDEXMATCH & XLOOKUP
12:29
Kenji Explains
Рет қаралды 104 М.
Common Excel Pivot Table Features People Miss (and you?)
12:45
Leila Gharani
Рет қаралды 163 М.
Say Goodbye to IF Statements in Excel with SWITCH
7:45
Teacher's Tech
Рет қаралды 589 М.
The Excel Functions Almost Everyone Overlooks (Better Than SUMIFS)
10:29
MyOnlineTrainingHub
Рет қаралды 105 М.