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!
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.
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!
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. 👍
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.
Thank you for showing the Choose function instead of an array of 1s and 0s which is what I've been using so far - and being able to rearrange columns plus the multiple column error handling! Brilliant!!
Had to watch this twice to get my head around it, mainly because I've never used the CHOOSE function. This works great so will definitely be using it. Thanks for the video
Love all of your content, extremely well explained. Have used CHOOSE() concept with FILTER() function from day 1. But to make things even more easier have used tables for source data and with INDIRECT() function refer to FILTER() function header row. When only you change FILTER() header value to existing source table valid header value job is done. INDIRECT() is volatile function but for not too big data sets works like a charm.
Literally one day ago I was trying to figure out how to rearrange columns but couldn't. Excellent timing on this video ;) What an amazing method. Thank you so much!
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 ?
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 😁
Really good- I have been using filter and index to do the same, your method is cleaner if it’s just say 2-3 columns you want returned from a large data set. But index I think is cleaner if you want to Omit only a few columns from a large data set. Thanks a lot- this is great.
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.
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
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
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
I think it says something about me when you said 'I hope you're excited to give it a try" and I thought "Yes, yes I am". Also, half-way through was thinking about a list of people who'd find great uses for it to share this with. Excellently presented as always, missed this one when it was released because we hadn't made the switch to 365 at the time.
It is so good tricks Mynda, thank you! I solved this Filter Rearrange Column Order sequence by using index&[Num_area] like below: =INDEX((C38:C42;D38:D42;E38:E42);;;MATCH($B$55:$D$55;$C$37:$E$37;0)) so we can find exact column values according to wanted list headers and using Filter as below: =FILTER(INDEX((C38:C42;D38:D42;E38:E42);;;MATCH($B$55:$D$55;$C$37:$E$37;0));B38:B42=C53) in that case, whenever you change header names like Price Item Quantity or Quantity Price Item into the header row of the target table, the Filter function shows correct values from the main database.
I vaguely remember there is another way to select columns, something with a string of ones and zeros, but this choose trick is far more elegant and allows for sequencing the columns exactly the way you want. Somehow this choose trick reminds me of the xlookup function.
@@MyOnlineTrainingHub Indeed, but that trick reminds me (as it did before) of the vlookup function: if the composition of the table changes, this trick will produce the wrong results or break down. That’s why I like the choose trick better: more robust.
The use of the CHOOSE function in combination with the matrix notation is sheer genius ! (didn't even know one could put table in the CHOOSE "values" argument...definitely redeem the usefulness of this function in my mind now, lmao)
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
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.
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
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.
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?
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?
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?
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
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.
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
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!!
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!
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})
Thank you. Great to know filter function based on data validation on various department. Can we include data validation list to select particular column (drop down list for Item, quantity and price).
Not sure what you mean, Shyam. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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?
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")))
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,
Thank you for your video’s. I’m really learning a lot albeit, it takes a few views for it to take hold in my brain. I have a question re filter function. I’ve created a quote / contract workbook. To keep it simple each product has its own worksheet which populates its own contract worksheet sheet using Filter function(Similar to your checkboxes video but I use qty instead to trigger TRUE). This works beautifully. On occasions 2 products need to be combined onto the one contract though. I’m struggling with how to combine the selected data from 2 different product worksheets into 1 contract worksheet or even if it is possible with the Filter function. Any Advice? cheers
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.
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
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.
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: ua-cam.com/video/Rzz9PyfwiVQ/v-deo.html
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)
Hi Minda, an excellent explanation as usual!... have a question, how do I create a combined filter & large formula for, let's say returning two criteria like the "top 10" selling items for a particular "subcategory". I just want it to return, the top 5,10, etc selling items for that subcategory...I am aware I can do it in a Pivot table easily, but I'd like to think outside the box and learn it this way. Thank you again!
Hi Pete, great question! The Top 10 can't be handled as just another criteria. However, using my file as an example and if you have Microsoft 365, you can use the new CHOOSEROWS function to return the top 2 quantities for Support like so: =CHOOSEROWS(SORT(FILTER(B11:F19,B11:B19="Support"),3,-1),SEQUENCE(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.
HI Mynda, I have been watching your videos and your tutorials are super helpful and mind blowing!! I have a question regarding refreshing a dashboard I created and included multiple PV tables. When updating the Q1 data to the Q2, the new dataset does not include the value corresponding to the filter set (ie. Q1), and this blocks the "refresh all" and it returned to an error "query the level object was not found in the cube". how to solve this without re-building all my pivot charts??? a big thank you!
Hi Helena, It's not clear where the filter was applied e.g. Power Query or the PivotTable. I won't see your reply here due to the volume of comments. Instead, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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).
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: ua-cam.com/video/aYLiUkCa4Ic/v-deo.html
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
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!
Thanks for sharing 😊
Thanks for that , the same pb in northern african countries
The expo on use of "FILTER and return non-contiguous columns" is a game changer for me ! Thank you Mynda
Glad you liked it, Lorenzo! 😊
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.
So pleased I could help!
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!
Glad it was helpful!
Thanks Mynda. You've turned my world upside down (in a positive way) yet again. Love your teaching style
Awesome! Thank you!
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
Thank you very much! 🤩
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. 👍
Glad you liked it 😊
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.
Great to hear 😊
@@MyOnlineTrainingHub Which version of Excel did you use on this video?
You can also use HSTACK instead of CHOOSE
Genuinely spent the last two days straight trying to figure out how to rearrange the columns.... thank you!!!
So pleased I could help, Nikki!
Thank you for showing the Choose function instead of an array of 1s and 0s which is what I've been using so far - and being able to rearrange columns plus the multiple column error handling! Brilliant!!
Glad you liked it!
FILTER CHOOSE is my new favorite thing about O365, until the next thing inevitably takes its place. Great video! 👍
Thanks so much! 😊
The truck for filtering not contiguous columns Is really great! The best I have seen so far
Wow, awesome to hear, Roberto!
The last segment on discontiguous columns saved me a lot of time, excellent video!
Great to hear!
This is so useful and amazingly well explained as usual.
Wish I could give 1000 likes to each of your videos.
Thanks so much, Ahmed 😊
good stuff, never thought of using the CHOOSE option for non-contiguous column returns.
I use CHOOSE inside of UNIQUE to get a unique list from non-contiguous columns. Didn't think of using it here either.
Cheers, Dayve!
CHOOSE is such a versatile function. Underrated IMO 😊
@@MyOnlineTrainingHub Do you have a training on CHOOSE?
Please see here: www.myonlinetraininghub.com/excel-choose-function
Had to watch this twice to get my head around it, mainly because I've never used the CHOOSE function. This works great so will definitely be using it. Thanks for the video
Great to hear, Steve!
The last part is amazing!! Filter and choose function work together. That’s cool~
Glad you liked it, Jeremy! 😊
Love all of your content, extremely well explained.
Have used CHOOSE() concept with FILTER() function from day 1.
But to make things even more easier have used tables for source data and with INDIRECT() function refer to FILTER() function header row.
When only you change FILTER() header value to existing source table valid header value job is done.
INDIRECT() is volatile function but for not too big data sets works like a charm.
Interesting use of INDIRECT 🤔 thanks for sharing.
Thanks Mynda. Thats exactly what I was looking for. I love the way you teach Excel!
Awesome 😊
Literally one day ago I was trying to figure out how to rearrange columns but couldn't. Excellent timing on this video ;) What an amazing method. Thank you so much!
So pleased to hear that 😊
Mynda Your simplification is awesome
Thank you! 😃
Very good way of illustrating how 'Filter' function works. Thanks 'MyOnlineTrainingHub' for teaching me nicely the Filter function. Please keep it up!
Glad it was helpful!
Thank you Mynda, very clear and well presented. Hoping to make the time and watch more of your videos and work. God speed and God bless!
Awesome to hear!
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 ?
Great to hear it was helpful! Correct, FILTER cannot be placed in an Excel Table. Dynamic arrays are not supported for Tables.
That's was great. Thanks Mynda. I too like the Choose option for the non-contiguous results.
Cheers, Matt!
Excellent lesson, I learnt a lot, thank you. One small point. Parentheses are both brackets ( and ). So, a bracket is ( or ) and parentheses are () 🙂
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 😁
Wow ! Amazing 🤩 ! I often use Filter function but I didn’t know some tips and tricks shared here. Thanks Mynda 🙏
Glad I could show you something new 😊
Excel in Office 365 has much more to offer than one can imagine. Wonderfully explained :)
Glad you think so!
Thank you Mynda for this great video and tip. Nice to use the choose function. Filter function saved me from using Index, small and row functions
Great to hear 😊
Really good- I have been using filter and index to do the same, your method is cleaner if it’s just say 2-3 columns you want returned from a large data set. But index I think is cleaner if you want to Omit only a few columns from a large data set. Thanks a lot- this is great.
Cheers, Martin!
It looks awesome.Would you filter the result including header instead of manually typing for header.
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.
Thanks Mynda , learnt a lot about FILTR function, your videos are full of knowledge.
Happy to hear that!
You just helped me out big time with rearranging the columns! Thank you from Prague. :)
Great to hear, Mark!
Thanks Mynda. Excellent review of the FILTER function. Everything is very practical and used.
Great to hear, Ivan 😊
The best video on filters. Congrats, and thankyou very much.
Glad you think so! 🙏
Thanks for CHOOS(E)ing to show us how to FILTER and return non-contiguous columns :)
My pleasure 😊
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
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
Perfect! Thank you!
Thanks, Mynda! This is a wonderful formula, and you have a very nice explanation for your viewers. We all viewer like it your tutorial. God Bless you
Thanks for your kind words, Karim!
Love the Non - Contiguous columns. I’m now starting to use Choose for the first time in my life LOL
Great to hear, Gary!
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
Thanks so much, Anthony! Yes, the FILTER with CHOOSE enables non-contiguous columns to be returned in any order.
I think it says something about me when you said 'I hope you're excited to give it a try" and I thought "Yes, yes I am". Also, half-way through was thinking about a list of people who'd find great uses for it to share this with. Excellently presented as always, missed this one when it was released because we hadn't made the switch to 365 at the time.
So pleased you can make use of it :-)
Wow, the last trick is really unexpected. Cool
Glad you liked it!
Such a clear explanation. Even I got it first time! Thanks.
Great to hear, Steven!
Filter and Choose a powerful combination! Thanks!
Glad you liked it 😊
Phenomal, clear explanation, taking us step by step. This filter function is great, and I love the bit at the end where you show non-adjacent columns.
Thanks so much, Anthony!
You made my day today. You are always someone I can rely on to learn something new. Thank you Mynda!
So pleased you find my videos helpful 😊🙏
This solves my immediate problem at work! Thank you so much for sharing.
Great to hear!
It is so good tricks Mynda, thank you!
I solved this Filter Rearrange Column Order sequence by using index&[Num_area] like below:
=INDEX((C38:C42;D38:D42;E38:E42);;;MATCH($B$55:$D$55;$C$37:$E$37;0)) so we can find exact column values according to wanted list headers and using Filter as below:
=FILTER(INDEX((C38:C42;D38:D42;E38:E42);;;MATCH($B$55:$D$55;$C$37:$E$37;0));B38:B42=C53)
in that case, whenever you change header names like Price Item Quantity or Quantity Price Item into the header row of the target table, the Filter function shows correct values from the main database.
Thanks for sharing, Emre!
Thanks for another great video. My question is can you do all this with Xlookup?
No, XLOOKUP doesn't return multiple columns and rows. XLOOKUP only spills one way, either across or down.
I had no idea this was possible in excel, you've saved me about 30mins every Monday! 😂
Great to hear!
I vaguely remember there is another way to select columns, something with a string of ones and zeros, but this choose trick is far more elegant and allows for sequencing the columns exactly the way you want. Somehow this choose trick reminds me of the xlookup function.
Yes, you can use FILTER in FILTER like this to return the 1st, 2nd and 4th columns: =FILTER(FILTER(B11:F19,B11:B19="Sales"),{1,1,0,1,0})
@@MyOnlineTrainingHub Indeed, but that trick reminds me (as it did before) of the vlookup function: if the composition of the table changes, this trick will produce the wrong results or break down. That’s why I like the choose trick better: more robust.
The use of the CHOOSE function in combination with the matrix notation is sheer genius ! (didn't even know one could put table in the CHOOSE "values" argument...definitely redeem the usefulness of this function in my mind now, lmao)
So pleased you liked it 😁
This video is great, really helpfull. Do you know how to SORTBY the output result please ?
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
What advantage does the FILTER function have over the older Advanced Filter, which I feel is quicker and just as accurate?
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.
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 ?
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
wow, great function, may i know how to set the filter can select more than one department?
Glad you like it! If you want OR operators in the FILTER criteria you use + between them e.g. =FILTER(range, (criteria1)+(criteria2))
Thanks for making our life more easier
It's my pleasure 😊🙏
Hi Mynda! Thankx for the filter+choose combo. Will use it. Cheers
Great to hear 😊
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.
Thank you! You can use VSTACK to append the headings. See this video: ua-cam.com/video/RebdtDzsMj8/v-deo.html
Thank you so much!!!! I have a project that I can try this on and I'm sure it will save me so much time. You're always a fantastic help.
Awesome to hear!
Always fun and exciting new formulas/functions and presented in an easy to understand fashion... thank you!
Glad you liked it 😊
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?
Thank you! Yes, you can simply reference the array in the if_empty argument.
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?
You can use transpose in power query for that.
FILTER UNIQUE and SORT - learn it, love it, live it
Definitely, Doug :-)
Does this work well with large data sets (e.g., 10,000+ rows)? Great video, thank you.
I'd think you're ok with 10k rows, but I haven't tested it.
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?
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
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.
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
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!!
Thanks so much, Wayne!
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?
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!
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})
Actually I was watching your video on dynamic ranges and figured our how to make this work with the Index function. Great videos!!!!
Awesome to hear you figured it out!
Great! Thank you for the video step by step.
Glad it was helpful!
Thank you. Great to know filter function based on data validation on various department. Can we include data validation list to select particular column (drop down list for Item, quantity and price).
Not sure what you mean, Shyam. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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?
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")))
Wow. This is very useful and so explicit. Thanks so much for sharing
My pleasure 😊
woah! great tip on minute 7:22 Mynda! 😁👌👌 thanks Mr. Excel !!! 🙏
Cheers! Glad you liked it 😊
Thank you so much for your amazing work as always, very comprehensive and understandable
Thanks for your kind words 😊
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,
Thank you! I believe so, but haven't tested wildcards.
Great, i liked Filter. Thank you Mynda!
Cheers, Luciano! 😊
@@MyOnlineTrainingHub For you too!
Thank you for your video’s. I’m really learning a lot albeit, it takes a few views for it to take hold in my brain.
I have a question re filter function. I’ve created a quote / contract workbook. To keep it simple each product has its own worksheet which populates its own contract worksheet sheet using Filter function(Similar to your checkboxes video but I use qty instead to trigger TRUE). This works beautifully. On occasions 2 products need to be combined onto the one contract though. I’m struggling with how to combine the selected data from 2 different product worksheets into 1 contract worksheet or even if it is possible with the Filter function. Any Advice? cheers
You could use the VSTACK or HSTACK functions to combine the data: www.myonlinetraininghub.com/excel-vstack-and-hstack-functions
@@MyOnlineTrainingHub Thank you. I’ll give it a try and let you know how it goes. In the meantime I review the link. Cheers
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.
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
Still used to Advanced Filter but may have to start using the filter function more.
FILTER is way better than advanced filter because is evaluates when the source data changes, whereas advanced filter has to be run again.
thank you! They removed my choosecols() function in an update, so i needed this!
I'd update again, because CHOOSECOLS is still available. Sounds like you may have had your version rolled back somehow.
Another great video. Thank you Mynda. As far as I Know FILTER function available for Office 2021 also besides Office 365.
Yes, it should also be in the new Office release.
Thank you, Mynda! Great tutorial I learned a lot. 🤗
I'm so glad!
omg. this is what I am looking for. thank you so much.
Wonderful to hear 😊
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.
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: ua-cam.com/video/Rzz9PyfwiVQ/v-deo.html
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)
Yes, love CHOOSECOLS and CHOOSEROWS. Unfortunately, when this video was recorded CHOOSECOLS wasn't out.
thanks for clear and shaep tutorial as always
You are welcome!
Hi Minda, an excellent explanation as usual!... have a question, how do I create a combined filter & large formula for, let's say returning two criteria like the "top 10" selling items for a particular "subcategory". I just want it to return, the top 5,10, etc selling items for that subcategory...I am aware I can do it in a Pivot table easily, but I'd like to think outside the box and learn it this way. Thank you again!
Hi Pete, great question! The Top 10 can't be handled as just another criteria. However, using my file as an example and if you have Microsoft 365, you can use the new CHOOSEROWS function to return the top 2 quantities for Support like so: =CHOOSEROWS(SORT(FILTER(B11:F19,B11:B19="Support"),3,-1),SEQUENCE(2))
This is truly amazing! Thanks for the detailed video, will definitely give it a try.
Great to hear 😊
So EXCELLENT! Thank you for another useful video!
My pleasure 😊
Great explained, solved my problem, Thank you very much
Glad I could help 😊
Thanks for your effort ... I have question , Can we use Countifs function as (crateria) in Filter formula ?
Yes, as long is the end result is a TRUE or FALSE.
Thank you, tutor. great lesson.
Glad you liked it!
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.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Very clear and useful video (as always!). Thank you!
Cheers, Tom!
Holly cow!
I tough I already knew all about filter, how wrong I’m
Thanks!
So pleased you learnt something new, Ricardo!
HI Mynda, I have been watching your videos and your tutorials are super helpful and mind blowing!! I have a question regarding refreshing a dashboard I created and included multiple PV tables. When updating the Q1 data to the Q2, the new dataset does not include the value corresponding to the filter set (ie. Q1), and this blocks the "refresh all" and it returned to an error "query the level object was not found in the cube". how to solve this without re-building all my pivot charts??? a big thank you!
Hi Helena,
It's not clear where the filter was applied e.g. Power Query or the PivotTable. I won't see your reply here due to the volume of comments. Instead, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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).
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: ua-cam.com/video/aYLiUkCa4Ic/v-deo.html
@@MyOnlineTrainingHub Thank you so much!!
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
I think you should filter then sum, not sum then filter.
@@MyOnlineTrainingHub Thank You. I helped.