Yes, FILTER is what I want to use too, but Advanced filter for most people in world who still don't have Excel 365, is that way go!!! For us, at least we have it up our sleeve as a party trick : )
Xmatch for the columns is pretty sharp- I like it! Filter is to Excel as Select is to SQL, it’s definitely the function that gives me the most right now!
🤯🤯🤯 It's been a while since I actually exclaimed vocally watching an Excel tutorial... Especially for an "Old School" method! I am humbled and excited by this underutilized (by me) advanced filter feature!! Thank you, Mike!! 👏
This one blows me away! Thanks Mike! I would choose Advance Filter because it's easier. I need to up my game and get to know filter. Loved all the keyboard shortcuts you used. Very impressive as always! Thanks again!
Speechless 😶!! Mind blowing!! Really smart solutions. Will explore that SEARCH function functionality I had not yet thought about. I have the feeling it will help me solve some data complex problems I have at work. Thanks for sharing. Not only do I learn a lot here but I also feel stimulated to further investigate new solutions.
Wow good to see Advanced Filter get some wow factor again! It's such a powerful, reliable and low performance feature. I love the new Filter and Sort formulas for lots of reasons especially for what they have done for cleaner, sorted drop down lists and the auto changing results but don't ditch advanced filter too fast, it has it's advantages a) handles the OR situation brilliantly incl wildcards b) handles changing criteria easily just type into cells and c) simpler to add in VBA. Ok the disadvantage is the results are the results and you can't build formulas from them and they don't update dynamically. That's where these Dynamic Array formulas are just awesome.
The keyboard shortcuts that you have applied are awesome. Advance filter is pretty much easier thou you it requires re-run if data has been changed. But the filter formular is a bit comprehensive but it requires some amount of time for practise before you can master it. Thanks Mike 🇹🇿 from Tanzania.
Great video (as always). In order to help it sink in I made the data block a table and then adjusted the references. In case it's helpful to anybody else: = FILTER( FILTER( Table1, (Table1[IndicatorName] = AW1) * ISNUMBER( SEARCH(Table1[Country], AW2))), ISNUMBER( XMATCH(Table1[#Headers], AX9:BI9))) Note that creating the table changes the year column headers to text. So you need to either change them or change the headers in AX9:BI9.
Truly amazing...opened to look into differently....using SERACH to get row values and XMATCH to get column values are awesome application within FILTER. earlier I used SEQUENCE, ROWS, INDEX to get specific column values but bow XMATCH is really easy-to-use 👌. Thanks Mike
Hi Mike. Awesome FILTER fun! That Advanced Filter trick is great, but I prefer the FILTER function for its dynamic behavior. Thanks for the Tuesday multi FILTER workout :)) Thumbs up!!
This is SOOO cool! I was just fiddlin' around with using LAMBDAs to extract data from a dataset (and actually succeding). Converting your dataset to an Excel Table and defining named ranges made my formulas look 'better'. Then I used the named ranges as variables in LAMBDA functions and in data validation dropdown lists, which made the output dynamic. The 'output' formula looks like this: =INDEX(OutputArray(B2;B3;B4;B5;B6);NumRows(B1);XMATCH(B11:M11;Headers)), where OutputArray is a LAMBDA function that filters the dataset based on the references, and NumRows is a LAMBDA that reads the number of rows in the filtered output. In the XMATCH function the lookup vallue is based on dropdowns in the cells B11:M11, and the lookup array is a named range based on the headers in the dataset. I had hoped that, maybe, I could have sent you a copy of my file, but couldn't find the necessary contact information.
Awesome video as usual! For me the most exciting ideas are 1) filtering to pull only the columns needed using the target header names instead of array ones and zeros {1,0,1,1,0}; and 2) being able to have all the matching items in one cell with delimiters. Unfortunately, I don't really understand how the Advanced Filter method works. Thanks again!
Mike - FILTER formula is something that one could still figure out somehow but how come you thought of using the trick with advanced filter. This is really out of the world. Amazing!! Did you bribe Microsoft to do this 😂😂?
With the FILTER function no need to “re-run the Advanced Filter” or anything else: it updates instantly. I prefer the New School solution, even though it requires some more elaborate logic (but not that much).
BTW: only last week did I use FILTER-formulas like these! :-) I participated in a strategic programme meeting with the board of directors where I manned a fully integrated, multi-sheet Excel workbook (of my re-cration) that allowed for fully dynamic “What If?”-scenarios that were run on the fly in the meeting, where we optimised a so-called ‘waterline diagram’ for a multi-year investment programme. Many consecutive scenarios were applied and everything updated in real time like a charm. It was a very high profile, fast lane meeting and everything went swimmingly. Some serious optimisation was performed and a consensus was reached. Received quite a few compliments afterwards. My Excel-skills have become undeniable across the board (if they weren’t already) and the new calculation engine has supercharged them only more! Love it! :-)
@@GeertDelmulle You are soooooooooo good with Excel, Teammate: everything updated in real time like a charm, swimmingly so to achieve consensus and cause the board to feel superchared optimal happiness!!!!! Undeniable Geert radness!!!!
@@excelisfun Thanks Mike. Admittedly, it did cost me working through the weekend and late evenings during the week. But everything had to be checked, all formulas reworked (some errors corrected), all this while the workbook was used by multiple people to provide the necessary inputs (SharePoint). I even added some logic to pinpoint inconsistencies in the inputs, so that people would correct those (using the usual tricks: formulas and conditional formatting). For us (you and me) not so hard to do, but the workbook is full of DA-calculations, double FILTER-formulas, LET-functions, running totals by means of DA-calculations (our work computers don’t have the newest insider functions), etc. So, I had to be careful about which functions to use and which not. I even discovered that one of the directors had to update his MS Office suite, because he didn’t have the LET-function. I even taught someone how to do “copy - Paste Values” (right-click, V). All the other stuff I could not teach - there was no time. Oh, and I protected most of the cells so that people would not mess up good formulas, etc. A decision that proved to be crucial in this multi-user exercise. Having Excel as a hobby really paid big dividends this time! It was great fun to do for me - like I said to several people at work: I had a blast! :-)
Hi Mike, thank you for a awesome tutorial. I like to play around and see if there are other ways as well. Not that I come up with better solutions. I would like to know what solution calculate faster between your solution and =INDEX(B10:AU3694,FILTER(SEQUENCE(ROWS(B10:AU3694)),(C10:C3694=AW1)*ISNUMBER(SEARCH(B10:B3694,AW2))),XMATCH(AX9:BI9,B9:AU9)). Yours is easier.
I just remoted into my other machine and timed. There was no difference. 57 mil sec average for each. I also added your clever formula to the download workbook. Go Team!!!
@@excelisfun I'm not master yet ..there is n number of things which I need to know...you are the one who helps me in the journey of mastering excel ...thanks a lot ❤️🤟
Can you explain how to do a formula to filter by a specific row criteria and also by column? I am trying to filter by criteria and also date set up like an exercise grid.
Hi Mike, excellent video and advanced filter is the way to go. Please when you have time can you do a video for Power Automation showing how to refresh an Excel file with Power Query and the sources files (TXT, CSV, ETC.) hosted on OneDrive. Thank you for all your help.
I will have to try this myself. I have never tried to host csv files at onedrive and then connect with Power Query. Is Power Automation a specific feature? I am not aware of it.
@@excelisfun Mike sorry I was trying to say Power Automate. I am trying to automate the refresh process of a few Excel files with Power Query that are using csv and other excel sources available on OneDrive. Thank you.
@@pcodello I made a video, but it is not about Power Automate. I do not know how to use Power Automate yet. I just took a look at it, but it was unclear to me. The video will show how to connect to folder with csv files at OneDrive.
@@excelisfun Mike the idea is to set up daily auto refresh to an excel file from a couple of files from different folders in OneDrive. I saw a video from Leila Gharani but I was hoping to see one from you, because you always drill down a little bit more on the details. Thank you.
Amazing Mike ... i have a question on Xmatch function (minute 4:50) ... , I thought you'll choose the "AX9:BI9" as lookup value for xmatch not "B9:AU9" ...but the formula worked fine .... am i missing something ?
You have to use the range that is the same size as the table being filtered in the lookup_value argument because then this function argument array operation delivers a results that is the same size as the table rows. Otherwise, it will not work.
@@excelisfun NO ...of course it didn't work with me ... i meant that the formula worked fine the way you done it ... but i got confused for a while ...what exactly is the lookup_array here for this xmatch !!!
Great video, really enjoy the content. Would it be possible to add another parameter to exclude any row in your final result that had a GDP under 10,000 in any resulting column?
Nice Mike. I was wondering if the advanced filter can be used to report on a table that has Columns (dates) and rows (tasks) and then on intersections it has hours spent for each task in each date. Either Dates or tasks can have more than 1 intersected value. The Report needs to show a row for each Date (Date, task, Hours) I have a sample Data but can't paste it here....
Hi Mike, in original data set when filter column C by GDP it shows 220 countries and four countries don't have GDP from 2000 - 2010 that means 216 countries have GDP from 2000-2021. But when you work out with formula it shows only 29 countries have GDP. Why other countries not showing up? Just for curiosity. Cheers
Hi, thanks for a great video - very interesting and powerful. Can we use a variable "include" argument of the FILTER function? I was hoping to filter a main data table (on one sheet) to one or more other sheets that relate to specific columns in the main data table. I wanted to name the sheets with that column header, extract the sheet name and then use that in the filter function. I tried to create the command, then use the INDEX function, but I don't think that it can handle the array/spill functions. It would be great if there were an INDEX function that could spill...
This is mind blowing Mike! Thanks for the tip. First time saw the use of XMATCH as column selector wrapped in a 2nd FILTER. I just played with your example workbook a bit and found that it seems you cannot choose randomly which column (Year) you wanted? Seems once you chose the first year, say 2000, the subsequent years have to be 2001, 2002, 2003, etc. i.e. in ascending order incremented by 1. You cannot choose say 2003, 1999, 2000? Any advice on this Mike? Thanks heaps!
Did you convert to an Excel Table? If so then the years become text and that causes problems. You might have to add zero to field name text years to get them back to years.
@@excelisfun Nah, I did not Mike. In your example workbook, if you change the years in Cell AY9 - BC9 to 2001, 2005, 2002, 2006, 2003 (originally was 2000, 2001, 2002, 2003, 2004 in your example workbook) the numbers returned for Argentina (I just picked one country here for simplicity) are not correct anymore?!
I have been trying to integrate this into data that I am averaging. The data includes zero values which I wan to further filter out. Its unclear how to adjust this to either include another filter to take out the zero values OR wrap an averageif around the the forumula. To be clear wrapping an average around it does not take out the zero values and its unclear how to use an averageif given the mix of column and row filtering. Any ideas?
Hi Mike, I don't know if this is the right way to ask a question, but I'm going to try anyway. Is there a way to display the page number from - to in a cell. I use the first rows as headers on every page and our ISO standard doesn't allow doing this in the footer (where it's very simple to do it). I have VBA code that can count the pages and know which page we are on. But this code doesn't work in the header story. Mike, would you mind helping me further?
I am pretty sure that is easy with the built in Page Seatup. Here is a video: ruclips.net/video/c_ZJLJK5PjM/видео.html Check in show more area below video for tip #26: 26. (47:52) Page Setup becasue the the page setup in at the 47:52 minute mark. Post back and let me know if that does the trick.
Hey Mike, thanks for the awesome content. I was wondering if there's a way in which you could use this filter function and then order the columns in a different order than the original table. I used the XMATCH to get the columns that I wanted, however if I scrambled the order of the field names in a different order than my original table, the XMATCH wouldn't display the data in the correct field names. (It will display the data in the same order of columns as the main table).
Hello Alejandro, Ran into a solution for this. You can use the Choose function inside Filter as demonstrated by Mynda Treacy at this RUclips link: ruclips.net/video/ZCQAweoAdOw/видео.html It's in the section about non-contiguous columns at the 7:57 mark. Hope this helps. M Smith
@@msmith3090 The use of Choose in the array section to select the columns before filtering is very interesting. In fact rather than creating choose the array {1,2,3,...} you can use sequence which even allows to count the cells in the column header. However the manual selection of each column on the other hand makes the formula very long as soon as you have +3 columns. What I suggest instead is to do an index of the table selecting all rows and the columns in the preferred order. =FILTER( INDEX(Table,SEQUENCE(ROWS(Table)),XMATCH(myreportcolumns, Table[#Headers]), condition to filter rows)
Hi Mike. I found a problem with this approach. Not in that it does not work but in that it doesn't necessarily return what is expected. The outer filter (on columns) will return the columns referenced in AX9:BI9 but not in the order specified in in AX9:BI9. For example, reverse the order of the years to list them from 2010 to 2000 in descending order (i.e. AY9=2010, AZ9=2009) and the data will still be populated according to the order of the original data set (i.e. 2000 then 2001). This would create an issue if the fields are not in the exact same order as the data would not match the header. Wondering if anyone has figured out a way to address this?
Follow Up: Here is the formula to use to have the data match the headers above. Not nearly as clean but in the end I think this is better and less chance of mistaking the data for matching the headers above. =LET(vFltr,FILTER(B10:AU3694,(C10:C3694=AW1)*ISNUMBER(SEARCH(B10:B3694,AW2))), INDEX(vFltr,SEQUENCE(ROWS(vFltr)),XMATCH(AX9:BI9,B9:AU9)))
Hi Mike, I have a question, not related to the video, about a situation I am in. In South Africa we have personal ID Numbers where the first six digits indicate your date of birth (881025) will mean that you were born on 25 Oct 1988. There are a number of ways I can extract the birth year from this number. The problem is if someone was born in 2000 or after your ID number will start with 001025 for instance if you were born in 2000 or 201025 if you were born in 2020. Excel does not see these dates as being in the 2000 to 2021 range but as dates in the 1900's. Is there a way I can get Excel to give me the right year?
@@excelisfun Hi Mike, the cut off is people entering the work place . Once you turn 18 you are legally allowed to seek employment. So if you were born in 2003 your employer would use your ID Number to see what year you were born and if you are old enough to work. So if you were born between January 2000 and September 2000 your ID will start with 0001?? and 0009??, October to December will start with 0010??, 0011?? or 0012??. 2001 would be 0101?? to 0109?? and 0110??, 0111?? and 0112??, the same with all dates up to 2009 (only 12 years old this year, so too young to work). The problem is that you cannot use the LEFT Function as Excel ignores all the zero's at the start of the number - the ID 001015 (born 2000-October-15th) will get the year of birth as 1911, but another date will be completely off the mark. You can start the number with an apostrophe and change it to a text entry, but this also fails at times.
@@Tommie_Rogers I guess you could use the hurdle date as: EDATE(TODAY(),-12*18), that would always jump back 18 years . LEFT on Text ID, like 000205, would pull 00, so I guess you have used custom number formatting!?!?! for the ID? So I guess I am unclear.
Excel-lent. My comment has nothing to do with the contents of the post (which is astounding). I'd rather address the problem of pronouncing the contributor's name. Bill Szysz is Polish. In Polish "sz" is pronounced exactly like "sh" in English. So "Szysz" should be pronounced "shish", as in: shish kebab. Thank you.
Thank you, Meni!!!! Shish : ) Bill Szysz has never corrected me in all the years he and I have been friends, so I never changed it. It might be that the way I pronounce it makes him sound more like the true super hero that he is lol
I don't know why I still love old school solution
perfect !!!
Yes!!!! Old School Rules!!!!!
Boom!As Im Using 365 I'd Go With The Filter Function But Gotta Say The Advanced Filter Solution Was Pretty Awesome...Thank You Mike :)
Yes, FILTER is what I want to use too, but Advanced filter for most people in world who still don't have Excel 365, is that way go!!! For us, at least we have it up our sleeve as a party trick : )
Xmatch for the columns is pretty sharp- I like it! Filter is to Excel as Select is to SQL, it’s definitely the function that gives me the most right now!
Perfect similie: FILTER can do both SQL SELECT (columns) and WHERE (rows)!!!! : )
🤯🤯🤯 It's been a while since I actually exclaimed vocally watching an Excel tutorial... Especially for an "Old School" method! I am humbled and excited by this underutilized (by me) advanced filter feature!! Thank you, Mike!! 👏
And, Hachi, the most amazing thing is that it has been there for over 30 years!!!!! That is true OLD S-cool : ) : )
This one blows me away! Thanks Mike! I would choose Advance Filter because it's easier. I need to up my game and get to know filter. Loved all the keyboard shortcuts you used. Very impressive as always! Thanks again!
You are welcome again, Scott!!! P.S. Keyboards Rule : )
The Advanced Filter Solution is so great!!
Glad it is so great for you!!!!
Inserting logical test to advanced filter is mindblown!!!
I learned something new today.
Thank you, Mike and Bill. Great teammmmmm
You are welcome!
Speechless 😶!! Mind blowing!! Really smart solutions. Will explore that SEARCH function functionality I had not yet thought about. I have the feeling it will help me solve some data complex problems I have at work. Thanks for sharing. Not only do I learn a lot here but I also feel stimulated to further investigate new solutions.
We do have fun here!!!!! I am so glad you are so pumped to explore and have even more fun, Jose!!!!!
Old School is Easier. This is life saving video for me. Thanks for posting .
Great! How was it life saving? Did you just have this issue come up?
@@excelisfun Yes I am planning to make all new series of videos on National Accounts of India. This video will save my Huge time.
@@simfinso858 Great : )
Wow good to see Advanced Filter get some wow factor again! It's such a powerful, reliable and low performance feature. I love the new Filter and Sort formulas for lots of reasons especially for what they have done for cleaner, sorted drop down lists and the auto changing results but don't ditch advanced filter too fast, it has it's advantages a) handles the OR situation brilliantly incl wildcards b) handles changing criteria easily just type into cells and c) simpler to add in VBA. Ok the disadvantage is the results are the results and you can't build formulas from them and they don't update dynamically. That's where these Dynamic Array formulas are just awesome.
That is a great summary! Very true : )
Also learnt one beautiful shortcut Ctrl+ > navigate to corners. I liked formula techniques it's easy to use
Yes, that corner jump is just what you need sometimes : )
I love this channel.
Glad you love it, Erick!!!!
The keyboard shortcuts that you have applied are awesome. Advance filter is pretty much easier thou you it requires re-run if data has been changed. But the filter formular is a bit comprehensive but it requires some amount of time for practise before you can master it. Thanks Mike 🇹🇿 from Tanzania.
What a great summary you just presented : ) Thanks, Kulava!!
Great video (as always). In order to help it sink in I made the data block a table and then adjusted the references. In case it's helpful to anybody else:
= FILTER( FILTER( Table1, (Table1[IndicatorName] = AW1) * ISNUMBER( SEARCH(Table1[Country], AW2))), ISNUMBER( XMATCH(Table1[#Headers], AX9:BI9)))
Note that creating the table changes the year column headers to text. So you need to either change them or change the headers in AX9:BI9.
This is simply mind blowing Mike. SUPER EXCELLENT :) Cheers :)
Yes!!!! Mind Blow in an Excel way is always good : )
Truly amazing...opened to look into differently....using SERACH to get row values and XMATCH to get column values are awesome application within FILTER. earlier I used SEQUENCE, ROWS, INDEX to get specific column values but bow XMATCH is really easy-to-use 👌.
Thanks Mike
You are welcome for the easy to use, CA Nirmal!!!
Hi Mike. Awesome FILTER fun! That Advanced Filter trick is great, but I prefer the FILTER function for its dynamic behavior. Thanks for the Tuesday multi FILTER workout :)) Thumbs up!!
You are welcome for the Tue Multi Filter Fun, Wayne!!!!
Yet another EXCELlent vieo. Thanks Mike.
You are welcome, long time teacher friend, Syed MM!!!!!!
This is SOOO cool! I was just fiddlin' around with using LAMBDAs to extract data from a dataset (and actually succeding).
Converting your dataset to an Excel Table and defining named ranges made my formulas look 'better'. Then I used the named ranges as variables in LAMBDA functions and in data validation dropdown lists, which made the output dynamic.
The 'output' formula looks like this:
=INDEX(OutputArray(B2;B3;B4;B5;B6);NumRows(B1);XMATCH(B11:M11;Headers)),
where OutputArray is a LAMBDA function that filters the dataset based on the references, and NumRows is a LAMBDA that reads the number of rows in the filtered output. In the XMATCH function the lookup vallue is based on dropdowns in the cells B11:M11, and the lookup array is a named range based on the headers in the dataset.
I had hoped that, maybe, I could have sent you a copy of my file, but couldn't find the necessary contact information.
Great formula! Good creative invention, Stein!! P.S. you do not need to send the file. I can see the full beauty from your post ; )
Awesome video as usual!
For me the most exciting ideas are 1) filtering to pull only the columns needed using the target header names instead of array ones and zeros {1,0,1,1,0}; and
2) being able to have all the matching items in one cell with delimiters.
Unfortunately, I don't really understand how the Advanced Filter method works.
Thanks again!
I have videos on Advanced Filter, but if you have Microsoft 365, then the FILTER is fully dynamic and the way to go!
Mike - FILTER formula is something that one could still figure out somehow but how come you thought of using the trick with advanced filter. This is really out of the world. Amazing!! Did you bribe Microsoft to do this 😂😂?
No, that the formula Advanced Filter has been in Excel for over 30 years. No bribe necessary lol
Excellent Mike!!!, thanks for all you do!
You are welcome, Most Awesome Teammate Chris : ) : )
Thanks Mike. The filter function rules for me.
Yes!!!! Ruling is always fun ; )
East or West! Mike is the best!
Thank you, Zahoor!!!!
you always figure out a way to make complicated task easy! Congratz!
You are so welcome, YFN Excel!!!
@@excelisfun im still gathering up courage... to do excel stuff online, I know that's kinda stupid
@@ExcelInstructor not stupid at all. You can do it!
@@excelisfun I finaly did it! its processing the HD quality right now...
Wish me luck ...
@@ExcelInstructor I wish you rad luck!!!!!!
Spooky ghost formulas! 👻👻👻👻👻👻👻
Yes!!!!! The Excel Ghost makes our formula life easy : )
I prefer the Mike's genius way!
I learned it from Bill Szysz, years ago ; )
Mind blowing , as always
Yes!!!!! Mind blown by Excel is always fun : )
Mike is a genius. #skills
Just a guy having fun with Excel lol
Boom! Thank you, Mike!
Boom! You are welcome : )
Excellent. Thanks Mike
You are welcome, Solex!!!
With the FILTER function no need to “re-run the Advanced Filter” or anything else: it updates instantly.
I prefer the New School solution, even though it requires some more elaborate logic (but not that much).
BTW: only last week did I use FILTER-formulas like these! :-)
I participated in a strategic programme meeting with the board of directors where I manned a fully integrated, multi-sheet Excel workbook (of my re-cration) that allowed for fully dynamic “What If?”-scenarios that were run on the fly in the meeting, where we optimised a so-called ‘waterline diagram’ for a multi-year investment programme. Many consecutive scenarios were applied and everything updated in real time like a charm.
It was a very high profile, fast lane meeting and everything went swimmingly. Some serious optimisation was performed and a consensus was reached.
Received quite a few compliments afterwards. My Excel-skills have become undeniable across the board (if they weren’t already) and the new calculation engine has supercharged them only more! Love it! :-)
Not too many people in my organisation know about Dynamic Arrays: only the ones who come to me for Excel Advice…
@@GeertDelmulle You are soooooooooo good with Excel, Teammate: everything updated in real time like a charm, swimmingly so to achieve consensus and cause the board to feel superchared optimal happiness!!!!! Undeniable Geert radness!!!!
@@excelisfun Thanks Mike. Admittedly, it did cost me working through the weekend and late evenings during the week.
But everything had to be checked, all formulas reworked (some errors corrected), all this while the workbook was used by multiple people to provide the necessary inputs (SharePoint). I even added some logic to pinpoint inconsistencies in the inputs, so that people would correct those (using the usual tricks: formulas and conditional formatting). For us (you and me) not so hard to do, but the workbook is full of DA-calculations, double FILTER-formulas, LET-functions, running totals by means of DA-calculations (our work computers don’t have the newest insider functions), etc. So, I had to be careful about which functions to use and which not. I even discovered that one of the directors had to update his MS Office suite, because he didn’t have the LET-function. I even taught someone how to do “copy - Paste Values” (right-click, V). All the other stuff I could not teach - there was no time. Oh, and I protected most of the cells so that people would not mess up good formulas, etc. A decision that proved to be crucial in this multi-user exercise.
Having Excel as a hobby really paid big dividends this time!
It was great fun to do for me - like I said to several people at work: I had a blast! :-)
@@GeertDelmulle Like a charm. I love that!!!!
How I love these new features:-)))
Thanks M(aster)ike ;-)))
Me too. L-O-V-E!!!!!!!!!
I love the old school here, lot of khnowledge :-)
Glad you like it!
filtering in excel made easy
Yes, indeed : )
You are great dear
Always glad to help, kishor!!!
Fantastic!
Glad you like it, Alex!!!
Hi Mike, thank you for a awesome tutorial. I like to play around and see if there are other ways as well. Not that I come up with better solutions. I would like to know what solution calculate faster between your solution and =INDEX(B10:AU3694,FILTER(SEQUENCE(ROWS(B10:AU3694)),(C10:C3694=AW1)*ISNUMBER(SEARCH(B10:B3694,AW2))),XMATCH(AX9:BI9,B9:AU9)). Yours is easier.
Thanks for the cool formula. I am not at my machine where I can time it till next week. I will also try to add your formula to download workbook.
I just remoted into my other machine and timed. There was no difference. 57 mil sec average for each. I also added your clever formula to the download workbook. Go Team!!!
@@excelisfun Thank you Mike for responding. Have a awesome day.
You are truly amazing
Glad you like the videos, Karol!!!
FILTER is magic!
FILTER is just like magic : ) : )
As always thanks
You are always welcome, Jayant!!!!
Amazing, thank you!
That adv filter formula seems way too easy. Only downside is that it doesn't update in real time but it's not that hard to remember to re-run it.
Well said. Perfect analysis of two methods. The Advanced Filter is just such a cool tool, with the ability to specify just come columns ; )
Amazing trick ❤️👌
Glad it amazing for you, Excel Master shubham ; )
@@excelisfun I'm not master yet ..there is n number of things which I need to know...you are the one who helps me in the journey of mastering excel ...thanks a lot ❤️🤟
@@shubhampawar8506 You are becoming an Excel Master because of your hard work and deduction : )
Can you explain how to do a formula to filter by a specific row criteria and also by column? I am trying to filter by criteria and also date set up like an exercise grid.
Another Question is :-Do You have video on How to Make "Chess sheet " in Excel??? 1 Black & 1 White cell 0 for black & 1 for conditional formatting?
I never thought of that before... 10 minutes later: Here is the formula: =MOD(ROWS(A$1:A1)+COLUMNS($A1:A1),2) I'll make a video next week for you : )
@@excelisfun Wow I am waiting.
Hi Mike, excellent video and advanced filter is the way to go. Please when you have time can you do a video for Power Automation showing how to refresh an Excel file with Power Query and the sources files (TXT, CSV, ETC.) hosted on OneDrive. Thank you for all your help.
I will have to try this myself. I have never tried to host csv files at onedrive and then connect with Power Query. Is Power Automation a specific feature? I am not aware of it.
@@excelisfun Mike sorry I was trying to say Power Automate. I am trying to automate the refresh process of a few Excel files with Power Query that are using csv and other excel sources available on OneDrive. Thank you.
@@pcodello I made a video, but it is not about Power Automate. I do not know how to use Power Automate yet. I just took a look at it, but it was unclear to me. The video will show how to connect to folder with csv files at OneDrive.
@@excelisfun Mike the idea is to set up daily auto refresh to an excel file from a couple of files from different folders in OneDrive. I saw a video from Leila Gharani but I was hoping to see one from you, because you always drill down a little bit more on the details. Thank you.
Amazing Mike ... i have a question on Xmatch function (minute 4:50) ... , I thought you'll choose the "AX9:BI9" as lookup value for xmatch not "B9:AU9" ...but the formula worked fine .... am i missing something ?
You have to use the range that is the same size as the table being filtered in the lookup_value argument because then this function argument array operation delivers a results that is the same size as the table rows. Otherwise, it will not work.
You got the formula to work the other way!?!?! Really?
@@excelisfun NO ...of course it didn't work with me ... i meant that the formula worked fine the way you done it ... but i got confused for a while ...what exactly is the lookup_array here for this xmatch !!!
Oooh ... i see ... its because you used another filter ... wow thanks mike ... it's easy now .... i got it
Great video, really enjoy the content. Would it be possible to add another parameter to exclude any row in your final result that had a GDP under 10,000 in any resulting column?
Thanks for sharing! 👍
You are welcome!
Nice Mike.
I was wondering if the advanced filter can be used to report on a table that has Columns (dates) and rows (tasks) and then on intersections it has hours spent for each task in each date. Either Dates or tasks can have more than 1 intersected value.
The Report needs to show a row for each Date (Date, task, Hours)
I have a sample Data but can't paste it here....
Hi Mike, in original data set when filter column C by GDP it shows 220 countries and four countries don't have GDP from 2000 - 2010 that means 216 countries have GDP from 2000-2021. But when you work out with formula it shows only 29 countries have GDP. Why other countries not showing up? Just for curiosity. Cheers
I do not understand your question. But there are definitely countries that have no GDP data in the start data set...
Hi, thanks for a great video - very interesting and powerful. Can we use a variable "include" argument of the FILTER function? I was hoping to filter a main data table (on one sheet) to one or more other sheets that relate to specific columns in the main data table. I wanted to name the sheets with that column header, extract the sheet name and then use that in the filter function. I tried to create the command, then use the INDEX function, but I don't think that it can handle the array/spill functions. It would be great if there were an INDEX function that could spill...
Superb sir, I would like to know what is your most favourite function in excel other than dynamic array functions
I have not favorites lol. I like them all : ) : ) : ) : )
@@excelisfun 😁
Super
Glad it is super for you, Jacek!!!
Good stuff.
Glad you like it!
Super sir
Glad you like it, vinayak!!!!
This is mind blowing Mike! Thanks for the tip. First time saw the use of XMATCH as column selector wrapped in a 2nd FILTER. I just played with your example workbook a bit and found that it seems you cannot choose randomly which column (Year) you wanted? Seems once you chose the first year, say 2000, the subsequent years have to be 2001, 2002, 2003, etc. i.e. in ascending order incremented by 1. You cannot choose say 2003, 1999, 2000? Any advice on this Mike? Thanks heaps!
Did you convert to an Excel Table? If so then the years become text and that causes problems. You might have to add zero to field name text years to get them back to years.
@@excelisfun Nah, I did not Mike. In your example workbook, if you change the years in Cell AY9 - BC9 to 2001, 2005, 2002, 2006, 2003 (originally was 2000, 2001, 2002, 2003, 2004 in your example workbook) the numbers returned for Argentina (I just picked one country here for simplicity) are not correct anymore?!
I’m wanna buy your excel textbooks but there is no on sale in Laos. How could I possibly be able to buy your excel textbook?
I have been trying to integrate this into data that I am averaging. The data includes zero values which I wan to further filter out. Its unclear how to adjust this to either include another filter to take out the zero values OR wrap an averageif around the the forumula. To be clear wrapping an average around it does not take out the zero values and its unclear how to use an averageif given the mix of column and row filtering. Any ideas?
Hi Mike,
I don't know if this is the right way to ask a question, but I'm going to try anyway.
Is there a way to display the page number from - to in a cell. I use the first rows as headers on every page and our ISO standard doesn't allow doing this in the footer (where it's very simple to do it). I have VBA code that can count the pages and know which page we are on. But this code doesn't work in the header story. Mike, would you mind helping me further?
I am pretty sure that is easy with the built in Page Seatup.
Here is a video: ruclips.net/video/c_ZJLJK5PjM/видео.html
Check in show more area below video for tip #26: 26. (47:52) Page Setup becasue the the page setup in at the 47:52 minute mark. Post back and let me know if that does the trick.
Both those filter functions seem pretty damn hard. Lol.
Yes, this is just a flat out hard data extract task. But imagine having to do it by hand. Which a lot of people do : ( Lol.
Hey Mike, thanks for the awesome content. I was wondering if there's a way in which you could use this filter function and then order the columns in a different order than the original table. I used the XMATCH to get the columns that I wanted, however if I scrambled the order of the field names in a different order than my original table, the XMATCH wouldn't display the data in the correct field names. (It will display the data in the same order of columns as the main table).
Hello Alejandro,
Ran into a solution for this. You can use the Choose function inside Filter as demonstrated by Mynda Treacy at this RUclips link: ruclips.net/video/ZCQAweoAdOw/видео.html
It's in the section about non-contiguous columns at the 7:57 mark.
Hope this helps.
M Smith
@@msmith3090 The use of Choose in the array section to select the columns before filtering is very interesting.
In fact rather than creating choose the array {1,2,3,...} you can use sequence which even allows to count the cells in the column header. However the manual selection of each column on the other hand makes the formula very long as soon as you have +3 columns.
What I suggest instead is to do an index of the table selecting all rows and the columns in the preferred order.
=FILTER(
INDEX(Table,SEQUENCE(ROWS(Table)),XMATCH(myreportcolumns, Table[#Headers]),
condition to filter rows)
AAAAAMMMMMAAAAAZZZZZIIIIIINNNNNNGGGGGG!!!!!
Hi Mike. I found a problem with this approach. Not in that it does not work but in that it doesn't necessarily return what is expected. The outer filter (on columns) will return the columns referenced in AX9:BI9 but not in the order specified in in AX9:BI9. For example, reverse the order of the years to list them from 2010 to 2000 in descending order (i.e. AY9=2010, AZ9=2009) and the data will still be populated according to the order of the original data set (i.e. 2000 then 2001). This would create an issue if the fields are not in the exact same order as the data would not match the header. Wondering if anyone has figured out a way to address this?
Follow Up: Here is the formula to use to have the data match the headers above. Not nearly as clean but in the end I think this is better and less chance of mistaking the data for matching the headers above.
=LET(vFltr,FILTER(B10:AU3694,(C10:C3694=AW1)*ISNUMBER(SEARCH(B10:B3694,AW2))),
INDEX(vFltr,SEQUENCE(ROWS(vFltr)),XMATCH(AX9:BI9,B9:AU9)))
Hi Mike, I have a question, not related to the video, about a situation I am in. In South Africa we have personal ID Numbers where the first six digits indicate your date of birth (881025) will mean that you were born on 25 Oct 1988. There are a number of ways I can extract the birth year from this number. The problem is if someone was born in 2000 or after your ID number will start with 001025 for instance if you were born in 2000 or 201025 if you were born in 2020. Excel does not see these dates as being in the 2000 to 2021 range but as dates in the 1900's. Is there a way I can get Excel to give me the right year?
Yes, but what is the hurdle? What is the cut off year that decides what goes in 2000 and what goes in 1900? 1930? 1950? or other?
I guess if the first 2 digits are year and 0-29 = 2000 and 30-99 = 2000, then this might get year: =IF(LEFT(I15,2)+0
Maybe for the whole date formula:
=DATE(IF(LEFT(I15,2)+0
@@excelisfun Hi Mike, the cut off is people entering the work place . Once you turn 18 you are legally allowed to seek employment. So if you were born in 2003 your employer would use your ID Number to see what year you were born and if you are old enough to work. So if you were born between January 2000 and September 2000 your ID will start with 0001?? and 0009??, October to December will start with 0010??, 0011?? or 0012??. 2001 would be 0101?? to 0109?? and 0110??, 0111?? and 0112??, the same with all dates up to 2009 (only 12 years old this year, so too young to work). The problem is that you cannot use the LEFT Function as Excel ignores all the zero's at the start of the number - the ID 001015 (born 2000-October-15th) will get the year of birth as 1911, but another date will be completely off the mark. You can start the number with an apostrophe and change it to a text entry, but this also fails at times.
@@Tommie_Rogers I guess you could use the hurdle date as: EDATE(TODAY(),-12*18), that would always jump back 18 years . LEFT on Text ID, like 000205, would pull 00, so I guess you have used custom number formatting!?!?! for the ID? So I guess I am unclear.
👍
: ) : )
👍🏻
Thanks, Carlos : )
Older Solution is Better
Excel-lent.
My comment has nothing to do with the contents of the post (which is astounding).
I'd rather address the problem of pronouncing the contributor's name.
Bill Szysz is Polish. In Polish "sz" is pronounced exactly like "sh" in English.
So "Szysz" should be pronounced "shish", as in: shish kebab.
Thank you.
Thank you, Meni!!!! Shish : ) Bill Szysz has never corrected me in all the years he and I have been friends, so I never changed it. It might be that the way I pronounce it makes him sound more like the true super hero that he is lol
@@excelisfun
My guess is that:
Either he has never heard you pronounce his name,
or he's been too shy to correct you.
@@meniporat3527 He has heard me said it 100s of times. I will have to ask his, now : )