The last segment on discontiguous columns saved me a lot of time, excellent video!
@MyOnlineTrainingHub
Жыл бұрын
Great to hear!
@vgb311
10 ай бұрын
Great! Thank you for the video step by step.
@MyOnlineTrainingHub
10 ай бұрын
Glad it was helpful!
@miiihaaas
3 жыл бұрын
Thanks for CHOOS(E)ing to show us how to FILTER and return non-contiguous columns :)
@MyOnlineTrainingHub
3 жыл бұрын
My pleasure 😊
@IvanCortinas_ES
3 жыл бұрын
Thanks Mynda. Excellent review of the FILTER function. Everything is very practical and used.
@MyOnlineTrainingHub
3 жыл бұрын
Great to hear, Ivan 😊
@lww8132
2 жыл бұрын
This solves my immediate problem at work! Thank you so much for sharing.
@MyOnlineTrainingHub
2 жыл бұрын
Great to hear!
@chrism9037
3 жыл бұрын
Awesome video Mynda!
@MyOnlineTrainingHub
2 жыл бұрын
Thanks so much, Chris 😊
@darrylmorgan
3 жыл бұрын
Hi Mynda!Great Tutorial On The Awesome FILTER Function...Thank You :)
@MyOnlineTrainingHub
3 жыл бұрын
My pleasure, Darryl! Glad you liked it 😊
@parahiamin6765
2 жыл бұрын
I have not used the FILTER function yet, however, I can see where it could be really useful. Great tutorial as always! PS "It's not case sensitive but I like to be proper" I do too, it's a pet hate of mine when people aren't and it's really annoying as it only takes a second!
@MyOnlineTrainingHub
2 жыл бұрын
😊 Glad you enjoyed it, Parahi!
@MaydayAggro
Жыл бұрын
I now use FILTER, UNIQUE, and SORT more than any other functions because they are so useful, especially when used together. (They can create an impromptu range acting as a PivotTable that auto-updates as new data are added to the source and is much easier to reference than the PT in subsequent formulas.)
@nandansubramanya
2 жыл бұрын
I had used INDEX(SEQUENCE, XMATCH) in a project. In that way, it picks up from the new column names automatically always In the downloadable excel, on line 56, replacing [CHOOSE({1,2,3},C11:C19,E11:E19,D11:D19)] with [INDEX(B11:F19,SEQUENCE(ROWS(B11:F19)),XMATCH(B55:D55,B10:F10))]. It picks up from column name. So, if I change column name in cell C55 from "Price" to "Department", the data is as expected. If tables are used, then tableName can be used instead of B11:F19 and tableName[#Headers] instead of B10:F10. So, in case the table columns are added , filter data will not be affected
@MyOnlineTrainingHub
2 жыл бұрын
Thanks for sharing, Nandan. I guess there's not many scenarios where the column headers will also change order sporadically, but good if you want that flexibility.
@Ahmed-fq6si
2 жыл бұрын
This is so useful and amazingly well explained as usual. Wish I could give 1000 likes to each of your videos.
@MyOnlineTrainingHub
2 жыл бұрын
Thanks so much, Ahmed 😊
@styftand
2 жыл бұрын
I think you need to adjust the your desk to avoid any neck and spine issues in the future.
@MyOnlineTrainingHub
2 жыл бұрын
Thanks for your concern, Stefan. I have a stand up desk :-)
@styftand
2 жыл бұрын
@@MyOnlineTrainingHub I meant the height of the monitors on the desk relative to your eyes, wasn't clear on the first reply.
@TonyDiem
7 ай бұрын
How can I build my {1,0,1} sequence dynamically and still use it in FILTER? I tried creating a string, = "{" & TEXTJOIN(",", X1:X5) & "}" but that doesn't seem to work. Any suggestions?
@MyOnlineTrainingHub
7 ай бұрын
Great question. I'd generate it with a formula that returns an array, but hard to say specifically without knowing your inputs. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@Compexcel
2 жыл бұрын
wuoa
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it :-)
@PCor18
6 ай бұрын
Disclaimer: If any European people have trouble getting this to work use {1\2} instead of {1;2} if the semicolon is your default separator. After I spent nearly an hour figuring out this wasn't working because differences in regional formatting I'm so grateful for this very, very elegant solution!
@MyOnlineTrainingHub
6 ай бұрын
Thanks for sharing 😊
@حسامكانو
7 күн бұрын
Thanks for that , the same pb in northern african countries
@indzara
3 жыл бұрын
Really liked the OR logic and the fact that everything >0 is treated the same. Nice design. Enabling multiple error values is another good tip. Thanks for sharing. 👍
@MyOnlineTrainingHub
3 жыл бұрын
Glad you liked it 😊
@lorenzoladejobi8701
2 жыл бұрын
The expo on use of "FILTER and return non-contiguous columns" is a game changer for me ! Thank you Mynda
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it, Lorenzo! 😊
@davidferrick
3 жыл бұрын
good stuff, never thought of using the CHOOSE option for non-contiguous column returns.
@JoelShafron
3 жыл бұрын
I use CHOOSE inside of UNIQUE to get a unique list from non-contiguous columns. Didn't think of using it here either.
@MyOnlineTrainingHub
3 жыл бұрын
Cheers, Dayve!
@MyOnlineTrainingHub
3 жыл бұрын
CHOOSE is such a versatile function. Underrated IMO 😊
@JoelShafron
3 жыл бұрын
@@MyOnlineTrainingHub Do you have a training on CHOOSE?
@MyOnlineTrainingHub
2 жыл бұрын
Please see here: www.myonlinetraininghub.com/excel-choose-function
@josecarlosconejo5724
3 жыл бұрын
Amazing!! I knew how to get only the columns that I wanted by wrapping the FILTER function with another FILTER and a set of 1s and 0s as criteria, but I had no idea about how to rearrange the column order. Very smart use of the CHOOSE function. I use FILTER a lot at work, but with your tutorial I will take it to a new dimension. Many thanks for sharing.
@MyOnlineTrainingHub
3 жыл бұрын
Great to hear 😊
@marlonmadrona1309
2 жыл бұрын
@@MyOnlineTrainingHub Which version of Excel did you use on this video?
@rusektor
7 ай бұрын
You can also use HSTACK instead of CHOOSE
@JeffreyWigington
Жыл бұрын
I did not know about the Choose function before! That is a really nice way to rearrange columns on the filter! I add seen another method using a double filter where the inner filter was on the whole table, but the second filter was a set of logicals [1,0,0,1] to "turn on" certain columns. It works well for removing unwanted columns, but doesn't let you rearrange columns. The choose function makes this so much cleaner. Thanks for your example!
@MyOnlineTrainingHub
Жыл бұрын
Glad it was helpful!
@duncanwil
Жыл бұрын
At around 2 minutes 25 secs you start a multi criteria example =FILTER(B11:F19, ... then you add (( ... )*( ... ))) Why the double parentheses when =FILTER(B11:F19,(B11:B19="Sales")*(C11:C19="Stamps")) is valid? Well, on my laptop anyway!
@MyOnlineTrainingHub
Жыл бұрын
Just habit, Duncan. 😊
@rafael_tg
Жыл бұрын
Thanks for the video. I tried this function CHOOSE and a similar with a FILTER outside the first FILTER. The double filter seems to be faster in a table with 400k rows. Has anyone also compared these two solutions?
@MyOnlineTrainingHub
Жыл бұрын
Interesting result. I haven't run any speed tests.
@MichaelBrown-lw9kz
7 ай бұрын
The way you used the CHOOSE function was brilliant. I would like to think I would have come up with this, but I don't know. Absolutely fantastic
@MyOnlineTrainingHub
7 ай бұрын
Thank you very much! 🤩
@Lyriks_
2 жыл бұрын
Hello Mynda thanks for another amazing tutorial, is this new trick is an alternative to using filter + xlookup-Match, since we primarly used that trick because we wanted to handle non contiguous data ? I'm refering to your xlookup video. Big thumbs up
@MyOnlineTrainingHub
2 жыл бұрын
Thanks so much, Anthony! Yes, the FILTER with CHOOSE enables non-contiguous columns to be returned in any order.
@philippemartinemorel1399
Жыл бұрын
Thanks for the very clear explanations ! I used the OR trick to add my initial table headers to the filtered result. Nevetheless it seems impossible that the filtered result becomes a table. Am I wrong ?
@MyOnlineTrainingHub
Жыл бұрын
Great to hear it was helpful! Correct, FILTER cannot be placed in an Excel Table. Dynamic arrays are not supported for Tables.
@williamarthur4801
2 жыл бұрын
I found the use of choose a bit confusing ( always have nothing personal) so tried this ; FILTER(INDEX(D4:G13,SEQUENCE(ROWS(D4:D13)),MATCH(Criteria Array, ,lookup Array,0)),D4:D13= Value ,"No Data" ) for the criteria array you can put the columns in whatever order you like and if you add to source it still wont matter. After a few more watches the penny dropped re re ordering using choose, still not my fav function.
@MyOnlineTrainingHub
2 жыл бұрын
Thanks for sharing, William!
@williamarthur4801
2 жыл бұрын
@@MyOnlineTrainingHub Thank you , you've made my day. ☺☺
@HuntBobo
6 ай бұрын
the font is very hard to read
@Seftehandle
3 жыл бұрын
Really liked or + (1+1) logic & and multiply (1*0)
@MyOnlineTrainingHub
3 жыл бұрын
Glad you liked it 😊
@gsracharya
3 ай бұрын
Wow, I was dying to filter only 3 column out of 15 columns, with table having hundreds of rows, and you have just given me the torch for my dark road. Thank you very much.
@MyOnlineTrainingHub
3 ай бұрын
So pleased I could help!
@jcethan2859
2 жыл бұрын
FILTER CHOOSE is my new favorite thing about O365, until the next thing inevitably takes its place. Great video! 👍
@MyOnlineTrainingHub
2 жыл бұрын
Thanks so much! 😊
@HuntBobo
6 ай бұрын
font cleared up later
@aviman2010
Жыл бұрын
Thanks Mynda. You've turned my world upside down (in a positive way) yet again. Love your teaching style
@MyOnlineTrainingHub
Жыл бұрын
Awesome! Thank you!
@wayneedmondson1065
3 жыл бұрын
Hi Mynda. Awesome examples! Love the {} trick for filling out the results table when no records are found and the CHOOSE trick to control the output format. Brilliant! Thanks for sharing :)) Thumbs up!!
@MyOnlineTrainingHub
3 жыл бұрын
Thanks so much, Wayne!
@eljinet
2 жыл бұрын
So on Google Sheets I have this: =iferror(FILTER('RAW DATA 2'!$B$2:$G,'RAW DATA 2'!$B$2:$B>=$B$1,'RAW DATA 2'!$B$2:$B
@MyOnlineTrainingHub
2 жыл бұрын
The last example explains how to do this using the CHOOSE function.
@manzoorofficial23
2 жыл бұрын
This is superb. But can Filter function be wrapped to a sumifs? Problem: I am working on a data set having Dates, Area, Customer, product and Amount. I want to filterout top Five Customers by Date and the problem here is all the fields have multiple Rows and i want to filter the customer after summing the corresponding amounts between dates. Is there a way to do this. Thanks
@MyOnlineTrainingHub
2 жыл бұрын
I think you should filter then sum, not sum then filter.
@manzoorofficial23
2 жыл бұрын
@@MyOnlineTrainingHub Thank You. I helped.
@ИлияГеоргиев-п9ф
7 ай бұрын
Great! But what about instead CHOOSE, use CHOOSECOLUMNS =FILTER(CHOOSECOLS(B2:E18;1;4);B2:B18=C53) I am using ";" because region setting 😬 or like that =CHOOSECOLS(FILTER(B2:E18;B2:B18=C53);1;2)
@MyOnlineTrainingHub
7 ай бұрын
Yes, perfect to use CHOOSECOLS. As mentioned to someone else who commented on CHOOSECOLS, this video was recorded before that function came out.
@Permik123
4 ай бұрын
Thank you for the great video MInda. We can use the new CHOSSECOLS function and make it even easier to Exctract Non-contiguous Columns. Like so '=FILTER(CHOOSECOLS(B11:F19;2;4;5);B11:B19=C53)
@MyOnlineTrainingHub
4 ай бұрын
Yes, love CHOOSECOLS and CHOOSEROWS. Unfortunately, when this video was recorded CHOOSECOLS wasn't out.
@dennistaylor
7 ай бұрын
Well presented, as always, but I would use the CHOOSECOLS function instead of CHOOSE, like this -- =CHOOSECOLS(FILTER(B11:E19,B11:B19=C53),2,4,3)
@MyOnlineTrainingHub
7 ай бұрын
Thanks! Yes, good idea. When this video was originally recorded CHOOSECOLS wasn’t out.
@anastasiortega3725
2 жыл бұрын
Combination of FILTER and CHOOSE: the clearest equivalent to sql sentence SELECT COLUMN1, COLUMN2, .... FROM TABLE WHERE COLUMN="VALUE" !!!!!. Thankyou
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it 😊
@billcolton6373
11 ай бұрын
Not sure if you can help or not, but I hope so. I keep track of my leagues fantasy football stats. What I want to do is figure out the top player with their ftps and apg. Here is the headings I have set up. Rank "PLAYER 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 GP FPts APG" Numbers 1 to 18 is their weekly score. Would the filter function do this?
@MyOnlineTrainingHub
11 ай бұрын
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@j.dasilva4567
Ай бұрын
The best video on filters. Congrats, and thankyou very much.
@MyOnlineTrainingHub
Ай бұрын
Glad you think so! 🙏
@schwarz8737
Жыл бұрын
Thanks for the video. Strangely enough, if I copy your exact FILTER(CHOOSE)-formula, it only returns one column consisting of "Stamps"; "4,99"; "15". So it somehow returns the first row of the Item-column, the second row of the Price-column, and the third row of the Quantity-column, as one column output. If I use FILTER(CHOOSECOLS) however, and set the array to be the first 4 columns of the table, and select columns 2; 4; 3, it does return the correct output: =FILTER(CHOOSECOLS(B11:E19,2,4,3),B11:B19=C53) Do you know why the regular CHOOSE formula returns only one column consisting of diagonal cells-references? If I single out the Choose function, it seems the curly brackets mess the formula up. If I choose just one column, it does return the entire column. If I ask it to return 2 columns between curly brackets, it returns the first entry of the first column, the second entry of the second column, followed by 7 # N/A's. I'm on EU English settings (so I have to use ; instead of , too)
@MyOnlineTrainingHub
Жыл бұрын
If you copied my formula exactly, then it would return the same results, so I can only think there is something different in your file. I'm happy to take a look if you want to post your question and sample Excel file on our forum: www.myonlinetraininghub.com/excel-forum
@edcooper1471
7 ай бұрын
Here's my issue with the filter function. It makes the excel spreadsheet so large that I can't email or share it. You can't even upload it to a Google Drive to share because it's so large, not to mention that it takes a much longer time to open up. I have a spreadsheet that only has 419 rows in it, and 13 columns. And the initial size of it, before I used only one "Filter" command, was 262kb; but after I used only one "Filter" command in it, it increased to a size of 37,837KB. So my question is, is it typical for an Excel spreadsheet to increase dramatically when we use these new functions, because I usually create spreadsheets (especially at work) to share with others and colleagues, but if these new functions are making these Excel spreadsheets so large that you can't do that, then wouldn't it make more sense to use the old methods of massaging the data instead of using these new functions?
@MyOnlineTrainingHub
7 ай бұрын
I'd love to see that spreadsheet as I suspect there are other issues contributing to the file size and sluggishness. I doubt it's the FILTER function on that small amount of data. Reach out via my website and I'll take a look: www.myonlinetraininghub.com/contact-us
@Ty-gr2tk
Жыл бұрын
This is awesome. Thank you. I am wondering if/how one would nest AND & OR functions. Using your example data, how could I specify that I want Sales AND Paper OR Pens? Is this possible?
@MyOnlineTrainingHub
Жыл бұрын
You can use the * operator for AND criteria and the + operator for OR criteria like so: =FILTER(B11:F19,(B11:B19="Sales")*((C11:C19="Paper")+(C11:C19="Pens")))
@Pravinamadoori
2 жыл бұрын
Hi, I am a beginner at excel. I have a column that contains the text. It has both numbers & text like 444469, 23432343,22289,mango,apple,docker,222789,python..etc. I am trying to filter rows to get only names but not the number rows. I tried number filter/text filter,, begins with.. kind of options.. but nothing is giving expected results. My expected result is to get rows with mango, apple, docker, and python.. but not others with only numbers in it.
@MyOnlineTrainingHub
2 жыл бұрын
You can add a column that looks for text in the cell, and then use that column for the filter. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@EarthEphemeralGems
2 жыл бұрын
Thank you very much for this video I really enjoyed it. I just subscribed. Can I use this function if I have to list and I want to see if any of the numbers from list one appear in list two?=FILTER(results_range, ISNUMBER(MATCH(name_range, name_list,0)) I was trying something like this, but it is not working as I had hoped. Thanks again for the great video.
@MyOnlineTrainingHub
2 жыл бұрын
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@mohammadj.shamim9342
2 жыл бұрын
Ma'am, I was looking if we can filter data based on a list. Say we have A2:D100. Here A2 is the name of localities in Adelaide. Now we make another list, F2:F6, of five localities. One way is to use "OR" and "AND" criteria. Is there any solution to filter the data using F2:F6?
@MyOnlineTrainingHub
2 жыл бұрын
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@aboluay9442
11 ай бұрын
Hi Dear, Amazing...... What if I have a very large number of items and I do not remember their full names... How can I filter by only part of the word?
@MyOnlineTrainingHub
11 ай бұрын
You can use wild cards *
@chunlamluk1865
8 ай бұрын
Why i followed the steps as 2:53, excel turned #CALC! results to me? is it about the version of EXCEL? i am using Microsoft 365
@MyOnlineTrainingHub
8 ай бұрын
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@sjh1956
3 жыл бұрын
As always, a great tutorial. However, when it comes to filtering non-adjacent columns, I have another solution. Instead of using the CHOOSE function, you can wrap your FILTER inside an INDEX formula like this: =INDEX(FILTER(B11:F19,B11:B19=C53),SEQUENCE(COUNTIF(B11:B19,C53)),XMATCH(B55:D55,B10:F10)) or "the other way around" like this: =FILTER(INDEX(B11:F19,SEQUENCE(ROWS(B11:F19)),XMATCH(B55:D55,B10:F10)),B11:B19=C53)
@MyOnlineTrainingHub
3 жыл бұрын
Thanks for sharing, Stein!
@dandeman1148
2 жыл бұрын
Nice. This one seems more like VBA Advance Filtering when using xlFilterCopy, CriteriaRange and CopytoRange :)
@xiphiaz
Жыл бұрын
I love your solution Stein, much cleaner and easier to reorder the columns. I did start the code with: "=IF(COUNTIF(B11:B19,C53)
@shariesprague183
Жыл бұрын
Is there a way to use "contains" so if a column includes "ABC" or "ABCDEF", the result would display both values?
@MyOnlineTrainingHub
Жыл бұрын
Hmm, FILTER doesn't accept wildcards, but you could possibly use the LEFT function to extract just the first 3 characters e.g.: =FILTER(B11:F19,LEFT(B11:B19,3)="ABC")
@vebe7109
7 ай бұрын
Great examples! Very insightful and detailed! Thanks! Would like to how can apply the examples presented if the data is located in tree or more sheets( 1 mill per sheet)? I am using nested xlookup- hoing to find a fater way?
@MyOnlineTrainingHub
7 ай бұрын
Thank you! I would use Power Query to consolidate the data in the 3 sheets and then do your lookup. Way more efficient. www.myonlinetraininghub.com/power-query-consolidate-excel-sheets
@henryhache5509
Жыл бұрын
thank you! They removed my choosecols() function in an update, so i needed this!
@MyOnlineTrainingHub
Жыл бұрын
I'd update again, because CHOOSECOLS is still available. Sounds like you may have had your version rolled back somehow.
@7absinth
7 ай бұрын
There is an error using FILTER OR function. It must exclude the match where it return value 2 because it is not OR anymore.
@osoriomatucurane9511
3 ай бұрын
I got your point. The exlanation goes beyond this tutorial. The Or logical operator evaluates to True if one of the conditions is True: 1. True False = True 2. True True = True 3. False True = True
@RamKumar-vb4et
Жыл бұрын
Great video. Thank you for sharing. A question: Can Filter command (in 365) handle wild-cat characters, or is there a way to do it, if we do not know exact values? Thanks,
@MyOnlineTrainingHub
Жыл бұрын
Thank you! I believe so, but haven't tested wildcards.
@RichardGetzPhotography
2 жыл бұрын
why can't you do OR on the same column??? Column A if there is found Support OR Sales THEN return values.
@MyOnlineTrainingHub
2 жыл бұрын
You can. OR is done with a + between logical tests instead of the *.
@mytube1369
20 күн бұрын
Help me, I am trying to use this with references to table columns.
@MyOnlineTrainingHub
19 күн бұрын
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@RamKumar-vb4et
11 ай бұрын
Great video once again. Thanks for sharing. A quick question - is there a way we can get columns headings as well depending on the columns chosen, whilst using filter formula? Thanks again.
@MyOnlineTrainingHub
11 ай бұрын
Thank you! You can use VSTACK to append the headings. See this video: kzitem.info/news/bejne/s5uYyqh6s6V-nW0
@TheSardOz
7 ай бұрын
Hi Minda, I have stumbled into an interesting behavior from the filter function. I am filtering some data (this are some trades) between 2 dates and using 2 criteria's account number and a bullian "Closed". It is very bizarre if I chose a date range, and on the last day of the date range, I have a trade that was open on that last day and then closed on following day (so the transaction to close the trade is out of range), the filter function will return also the data for that trade closed on the following day. So out of the range chosen, and it will return specifically only the matching trade to close the position and no other data from the out-of-range date. What is happening here? any ideas...?
@MyOnlineTrainingHub
7 ай бұрын
I'd have to see your data structure. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@TKae421
7 ай бұрын
Does this work well with large data sets (e.g., 10,000+ rows)? Great video, thank you.
@MyOnlineTrainingHub
7 ай бұрын
I'd think you're ok with 10k rows, but I haven't tested it.
@n_mckean
Жыл бұрын
I have an extra challenge that I can't seem to resolve. Essentially my "Master List" that is going to be filtered only contains partial columns. One a separate worksheet I filter based on a rule (e.g. date = March) but then on that worksheet, I want to add extra columns with extra detail. I want document users to enter the data into those worksheets and not into the master. But I can't find a way to do this where when the filtered rows change or reorder, the appended columns also reorder. Equally, if I put those columns in the master and include them in the filtered range, any attempt to 'overwrite' the values in the detail worksheet breaks the filter with a SPILL error.
@MyOnlineTrainingHub
Жыл бұрын
Interesting question. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@traciesmobile683
8 ай бұрын
I have come across an issue with filter function. I have a spreadsheet I use for my finances. On it I have a lookup which shows which bills are due this week. I have a table which contains the week number. The filter function searches for this week's number and returns any bills due. Today I noticed that it doesn't just return items in week 1 but also items in other rows containing a 1. I want it to only return values which match the entire week value. Any ideas?
@MyOnlineTrainingHub
8 ай бұрын
Sounds like your filter is using the SEARCH or FIND function to identify rows containing 1, instead of a simple =1. Hard to say without seeing your file. If that's not it, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@garyevans7620
7 ай бұрын
Excellent lesson, I learnt a lot, thank you. One small point. Parentheses are both brackets ( and ). So, a bracket is ( or ) and parentheses are () 🙂
@MyOnlineTrainingHub
7 ай бұрын
In Australia and the UK (and probably elsewhere) this ( is a bracket and these () are brackets. My understanding is in the US this ( is a parenthesis and these ( ) are parentheses. Usually the term bracket/s are reserved for square brackets [ ]. But I’m not American, so feel free to correct me if you are 😁
@tommyharris5817
2 жыл бұрын
What advantage does the FILTER function have over the older Advanced Filter, which I feel is quicker and just as accurate?
@MyOnlineTrainingHub
2 жыл бұрын
The biggest advantage is that the FILTER function will automatically display changes in the source data, whereas the old Advanced Filter tool has to be manually re-run to get updates.
@sureshrouniyar3482
2 жыл бұрын
It looks awesome.Would you filter the result including header instead of manually typing for header.
@MyOnlineTrainingHub
2 жыл бұрын
If you include the header in the filter range it probably wouldn't satisfy the criteria and if you want to sort the data, then the headers will be somewhere random in the resulting data.
@lopher70
2 жыл бұрын
Holly cow! I tough I already knew all about filter, how wrong I’m Thanks!
@MyOnlineTrainingHub
2 жыл бұрын
So pleased you learnt something new, Ricardo!
@angebeda6739
Жыл бұрын
This video is great, really helpfull. Do you know how to SORTBY the output result please ?
@MyOnlineTrainingHub
Жыл бұрын
Glad to hear 🙏 SORTBY tutorial here: www.myonlinetraininghub.com/excel-sortby-function If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@RobertPereira-l1c
11 ай бұрын
Hi Mynda, very nicely explained video. I have one question though. I read all the comments and couldn't find a solution for this. When using the Filter Function and the criteria is not met, I would like the filter to show all the rows in the array as if there is no filter at all. is that possible?
@MyOnlineTrainingHub
11 ай бұрын
Thank you! Yes, you can simply reference the array in the if_empty argument.
@mohammedalkhatib7529
Жыл бұрын
Thank for this great tutorial. How can execute filter (filter 2) on a result of filter ( filter 1) and then execute another filter ( filter 3) on a result of the second filter. I don't want to use AND /OR.
@MyOnlineTrainingHub
Жыл бұрын
You can nest FILTER functions. If you have further questions, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@AbdulRahman-em7pc
Жыл бұрын
Can we add the unique or other dynamic range end with # inside the filter formula as a condition in include part of the formula, i tried but it shows error. And one more question is that can we use two different conditions one vertical (data ) and another is horizontal (column / header ) as a AND condition in filter formula. Currently i am working on FS reports , its vermich needed for dynamic reports.
@MyOnlineTrainingHub
Жыл бұрын
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@mathiasburkard2403
3 жыл бұрын
Thanks Mynda. Thats exactly what I was looking for. I love the way you teach Excel!
@MyOnlineTrainingHub
3 жыл бұрын
Awesome 😊
@juanrsosa
6 ай бұрын
Great info, Thanks! What if you want to see all departments with totals for all Items Stamps 500 Paper 60 Pens 90 Is it possible?
@MyOnlineTrainingHub
6 ай бұрын
Yes, but not with FILTER. You can do it with some of the newer Dynamic Array functions, but honestly, I'd just use a PivotTable. It's way quicker and easier!
@yandisafanqa6050
2 жыл бұрын
Thank you for this, how do you return or re-arrange the data into rows instead of columns? For example instead of having Department, item....in column order, it is rearranged to row order? I want to re-arrange the data received on excel online via MS forms from column order to row order?
@MyOnlineTrainingHub
2 жыл бұрын
You can use transpose in power query for that.
@truewiking
2 жыл бұрын
Great explained, solved my problem, Thank you very much
@MyOnlineTrainingHub
2 жыл бұрын
Glad I could help 😊
@alicemorili1330
3 жыл бұрын
This is great
@MyOnlineTrainingHub
2 жыл бұрын
Thanks, Alice!
@UtuDudas
11 ай бұрын
Filter is of late my favourite function. I only wonder if it is possible to do individual calculations inside the spilled array, like a product of each individual value with a number outside of it.
@MyOnlineTrainingHub
11 ай бұрын
Awesome to hear! I'm sure you can do what you want. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@Commander_Chief
Жыл бұрын
In column 'a' I have 200 on entries of only 6 types of products. If I wanted to only see 3 types of these products in a pivot with slicer how could I do that
@MyOnlineTrainingHub
Жыл бұрын
You can filter the PivotTable to show the Top 3 items. If you're stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@tommyharris5817
2 жыл бұрын
Thanks for another great video. My question is can you do all this with Xlookup?
@MyOnlineTrainingHub
2 жыл бұрын
No, XLOOKUP doesn't return multiple columns and rows. XLOOKUP only spills one way, either across or down.
@minamishail_Tech2024
9 ай бұрын
Really good, what if I need to filter a specific column based on a list of data or a named range where it's value change from time to time ?
@MyOnlineTrainingHub
9 ай бұрын
Glad you liked it. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@רמיששונוב-פ3ע
Жыл бұрын
Hii thank you But what if the numbers of columns are dynimic And i want to change it in the brackets?
@MyOnlineTrainingHub
Жыл бұрын
I'm sure it can be done, but without knowing your data layout it's difficult to help specifically. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@davidfamilydoctor9430
Жыл бұрын
I have used the * and the + before in another context, but did not fully comprehend why it worked until now.
@MyOnlineTrainingHub
Жыл бұрын
Glad I could help!
@keremtosyali7614
2 жыл бұрын
Hi Mynda. Video is great. ONe question. when I filter the table to a new sheet the colors of the cells disappear. how can we keep the colors or how can we add a criteria to chose only specific coloured cells.
@MyOnlineTrainingHub
2 жыл бұрын
Hi Kerem, the colours are not something a formula can replicate, however you can use Conditional Formatting to format cells based on criteria you specify: kzitem.info/news/bejne/s7Cwn4Svn6maiYY
@netbuddy1089
Жыл бұрын
Is there a way to choose two department in data validation list i.e sales and support, if not using data list then how we make it dynamic and not fix in formula (many thanks in advance)
@MyOnlineTrainingHub
Жыл бұрын
Not easily. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@zubairso
2 жыл бұрын
This is truly amazing! Thanks for the detailed video, will definitely give it a try.
@MyOnlineTrainingHub
2 жыл бұрын
Great to hear 😊
@amospan14
10 ай бұрын
Very helpful Mynda, thank you very much! =)
@MyOnlineTrainingHub
10 ай бұрын
Glad it was helpful! 😊🙏
@omarmmzn
Жыл бұрын
followed it tp the letter and it wont work. When I select the columns it just appears as normal text not part of the function.
@MyOnlineTrainingHub
Жыл бұрын
That sounds odd. I'd love to help troubleshoot. Please post your question and Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@luapnagle
2 жыл бұрын
Is FILTER the FILTER better than CHOOSE ? , its a smaller formula especially for a large column data set ? Both options below only show columns B-C-E after a filter match on (G5) columns are: - B-C-D-E OPTION 1 =FILTER(FILTER(B5:E10,B5:B10=G5),{1,1,0,1}) OPTION 2 =FILTER(CHOOSE({1,2,3},B5:B10,C5:C10,E5:E10,E5:E10),B5:B10=G5) I
@MyOnlineTrainingHub
2 жыл бұрын
Good question. There are no efficiencies to be gained simply because a formula has less characters than another formula. Rather it's dependent on how the functions being used are coded in the back end of Excel. Without testing on large datasets I couldn't say if FILTER(FILTER is more or less efficient than FILTER(CHOOSE. One key difference between these two options is FILTER(CHOOSE can rearrange the order of the columns output, whereas FILTER(FILTER cannot.
@luapnagle
2 жыл бұрын
Thanks for the response, not being able to change the order is a good call.... Still amazes me with Excel that there is a hundred ways to achieve the same result. I guess no one way is better than the other, they all have there merits. Carry on the great work you are one of my top favourite channels.
@arkai59
3 жыл бұрын
Thank you so much for your amazing work as always, very comprehensive and understandable
@MyOnlineTrainingHub
3 жыл бұрын
Thanks for your kind words 😊
@pvrameshbabu
7 ай бұрын
Thanks. It's a very useful function.
@MyOnlineTrainingHub
7 ай бұрын
Glad it was helpful!
@chubbydawme
2 жыл бұрын
can you return one column but based on two criteria? Say you want to return just the Department column whose item is Stamp?
@MyOnlineTrainingHub
2 жыл бұрын
Yes, like so: =FILTER(B14:B22,C14:C22="Stamps") Note: you only listed one criteria i.e. Stamps. If you want multiple criteria then you would write it like this: =FILTER(B14:B22,((C14:C22="Stamps")*(B14:B22="Sales"))
@mauricestillsecret4807
2 ай бұрын
is there a way to get automatically under the array the sum of the total revenue $?
@MyOnlineTrainingHub
2 ай бұрын
You could use VSTACK to add the total row. www.myonlinetraininghub.com/excel-vstack-and-hstack-functions
@ingogellrich9993
10 ай бұрын
Hi all I am totally confused. I have this version of Excel: Excel® für Microsoft 365 MSO (Version 2308 Build 16.0.16731.20310) 64 Bit , German language When I try to use the FILTER(CHOOSE{})) combination, it returns me the first colum I specify, only - no matter how many indexes and columns I define in CHOOSE function. When I try to use FILTER(FILTER();{}) combination, it returns only selected rows specified by the constant array in the outer FILTER function. With German language settings in Excel, I cannot use commas as in given examples for parameters, but semicolons... Has anybody encountered the same problem? Kind regards Ingo
@ingogellrich9993
10 ай бұрын
oh damn.... localization of formulas is SOOOO stupid for anyone with the same problem - with German settings you need to separate array values by a dot, e.g. CHOOSE({1.2.3}... or FILTER(FILTER();{1.0.1})...
@MyOnlineTrainingHub
10 ай бұрын
Thanks for sharing. I knew of semi-colon instead of comma, but I didn't know about dot instead of comma in arrays.
@stephenmascarenhas1987
8 ай бұрын
Well presented with some intricacies in the formulae's
@MyOnlineTrainingHub
8 ай бұрын
Thank you 😊
@marywise5943
3 жыл бұрын
This is great! I can think of several uses where I can apply this filter method. I would also love to see how you would select the columns based on a value example of something like
@MyOnlineTrainingHub
3 жыл бұрын
Yep, that's easy to do, Mary. Just reference the value column and apply the logical test in your criteria arguments of FILTER. If you get stuck you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@marywise5943
3 жыл бұрын
Perfect! Thank you!
@ExecutiveAssistant-z5b
8 ай бұрын
Mynda, This is an excellent example of using the filter function which I'm using extensively at my job. Is there a way to get either choose or wrapping the filter function in another filter function to work? Here is function call that I'm trying to use =filter(filter(array,isnumber(search("string", array))),{0,1})
@ExecutiveAssistant-z5b
8 ай бұрын
Actually I was watching your video on dynamic ranges and figured our how to make this work with the Index function. Great videos!!!!
@MyOnlineTrainingHub
8 ай бұрын
Awesome to hear you figured it out!
@julianizdebski5035
10 ай бұрын
Can I do a dynamic range for the filter? I have a table, that grows and shrinks... Is it possible to do a dynamic range? I tried offset in the "=Filter(A1:C(offset.....))" but that won't work - any magic tricks?
@MyOnlineTrainingHub
10 ай бұрын
Yes, you can do a dynamic range, but it's not written like that. See this tutorial: www.myonlinetraininghub.com/excel-dynamic-named-ranges
@julianizdebski5035
10 ай бұрын
@@MyOnlineTrainingHub thank you - your the bomb for replying ! Will check it out
@Fabi_terra
7 ай бұрын
Thank you, Mynda! Great tutorial I learned a lot. 🤗
@MyOnlineTrainingHub
7 ай бұрын
I'm so glad!
@giusepperoberti
Ай бұрын
Hi and thanks for this video. Is it possible that the "filter formula" updates automatically if you wish to add extra data? (The rows below of the top part).
@MyOnlineTrainingHub
Ай бұрын
Sure, if you store your source data in an Excel table, FILTER will automatically include new data added to the Table. See this video on tables: kzitem.info/news/bejne/wo-Cz4mhfJNlfJg
@giusepperoberti
Ай бұрын
@@MyOnlineTrainingHub Thank you so much!!
@choicepark3072
Жыл бұрын
how to sort a filter resulted spilled array based on non spilled column?
@MyOnlineTrainingHub
Жыл бұрын
Try using the SORTBY function: www.myonlinetraininghub.com/excel-sortby-function
@leoceoliveira
Жыл бұрын
Wow, it took me awhile to understand that in my version of excel (365) the syntax for curly braces {} is \ and not ,. For example, I had to type {1\2\3} and not {1,2,3} in order to choose the columns I wanted. Why on earth wouldn't Microsoft adopt a SINGLE convention for these notations and syntaxes. I hope this helps.
@MyOnlineTrainingHub
Жыл бұрын
Glad you figured it out. I think the different notation is a result of the differences in the ways a period and comma are used for decimals and thousand separators etc., but that's a guess 😉
@leoceoliveira
Жыл бұрын
@@MyOnlineTrainingHub It could be. BTW, super helpful video!
@oracle998
2 жыл бұрын
wow, great function, may i know how to set the filter can select more than one department?
@MyOnlineTrainingHub
2 жыл бұрын
Glad you like it! If you want OR operators in the FILTER criteria you use + between them e.g. =FILTER(range, (criteria1)+(criteria2))
Пікірлер: 453