Mike, This video solved a huge prolem for me with a client. Thank you. The main formula is one I would not have even considered. I have gotten so involved with PQ, cubes and data tables. Those would not have worked with this particular project.
@excelisfun
9 ай бұрын
If you have Microsoft 365, then this task is MUCH easier. Here is a video: kzitem.info/news/bejne/xZ6Xl4htpqR3lY4
@franreed5382
9 ай бұрын
@@excelisfun I agree. Wouldn't not have been able to figure out without your video. I have MS365 and knew this was a bit old school but it still works.... As it turns out Client is still on Excel 2016..so all worked out. Like the new video.
@excelisfun
9 ай бұрын
@@franreed5382 Almost every time I use the new easier functions, I remind myself of the older harder ones. This is good, because like you, I have to create solution for many people who don't have the new and glorious M 365 Excel : )
@jacobsprague4235
6 жыл бұрын
Absolutely awesome. No idea how anyone could know this much about excel, but I am very grateful that you are willing to share with so many of us. God bless!
@ujwaldey4270
8 жыл бұрын
You and Sal Khan are pioneers and your work will live long after you.
@excelisfun
8 жыл бұрын
Thank you for the kind words! : )
@danizavasquez2729
7 ай бұрын
You´ve made my life so much easier over the past years, thank you!!! I still remember the formula with small and curly brackets, you´re always improving things. I was wondering if there´s an argument you can add to make the empty cells show empty in the new sheets instead of throwing a "0" value?
@vijaysahal4556
3 жыл бұрын
amazing mike thanku for your all unique topics share with us 💯💯💯💪💪💪💪💪💪💥💥💥🔥🔥🔥🔥🔥🔥👌
@excelisfun
3 жыл бұрын
Yes, I do have a lot of different topics so all of our Team can have fun : ) : ) : ) Thanks for your amazing support, Vijay : ) : ) : ) : )
@stevejeffries977
3 жыл бұрын
Clearly four years down the line I'm very late to the party here. However, this just today in 2021 happened to be exactly what I was looking for and it worked like a dream. Thank you so much.
@excelisfun
3 жыл бұрын
Glad it helped : )
@sarahalvarez2343
3 жыл бұрын
Mind. Blown. Beyond thanks!!
@nadermounir8228
3 жыл бұрын
Locking the columns when copying over using excel table nomenclature is an amazing trick. I searched for this video to be able to use it for my spreadsheet at work. Thank u Mike for these valuable tricks and tips. I am very grateful that you are sharing with us all this..... Great tutorial 👌 👍 👏
@BillSzysz1
8 жыл бұрын
I don't know what is better - beautiful formula or the perfect explanation. Both are awesome!!! Thanks
@excelisfun
8 жыл бұрын
You are welcome, 'O PQ Poet Bill Szysz!!!
@akshayhandoo
2 жыл бұрын
Awesome trick Mike. I am really impressed and love the way you teach. It’s really helpful for newbies like us to learn these techniques for free ( thumbs up added 😀). Your content stays relevant for years after publishing the videos.
@excelisfun
2 жыл бұрын
Glad to help, Akshay!!!!
@akdhunt
5 жыл бұрын
This is one of the most ingenious and clever ways to use Excel, kudos!
@excelisfun
5 жыл бұрын
Glad this video helps, Enrique!!! Thanks for the support with your comment, thumbs up and your Sub : )
@Sal_A
8 жыл бұрын
The infamous array formulas...love it. A quick easy approach is to create a pivot table and use the option Show Report Filter Pages
@excelisfun
8 жыл бұрын
I totally agree: Options, Show Filter Pages is almost THE best feature in all of Excel!!!! However, because PivotTables aggregate, if there were any records that contains the same data for each column (a type of duplicate record that was not extraneous), the PivotTable Options, Show Filter Pages method would not achieve the goal of Extracting a Complete List of Records. The Array Formulas and Power Query Method would achieve the goal of Extracting a Complete List of Records regardless of whether or not there were duplicates. In general, PivotTables was not a toll designed to extract records (like Filter, Advanced Filter, Array Formulas, Power Query), but instead to make aggregate with conditions or criteria.
@Sal_A
8 жыл бұрын
I totally agree with you said. Thanks MG!!
@jerrydellasala7643
4 жыл бұрын
Trick to get worksheet name is great! Amazing how much easier this is in Excel 365: =SORT(UNIQUE(FILTER(fSales,fSales[Product]=B1),TRUE,TRUE)) One formula, first worksheet can be copied for each product and changing just the worksheet name. Awesome!
@excelisfun
4 жыл бұрын
Yes, the Old School is long and old ; )
@ashlielepek
4 жыл бұрын
Thank you!!!! Thank you!!! Thank you!!!! This saved me hours of work today and in the future
@excelisfun
4 жыл бұрын
You are welcome, welcome, welcome!!!
@56457645754
6 жыл бұрын
I don't what to tell you man. PURE GENIUS!
@prakashrao3607
3 жыл бұрын
Unbelievable! I never imagined one could do such in excel without VBA codes!!
@maneshzaveri6277
8 жыл бұрын
Great video - very relevant for accountant who needs to extract data all the time ....cant wait for the Power Query video ...Thanks.
@excelisfun
8 жыл бұрын
You are welcome, fellow Accountant!
@douglaszulu6281
8 жыл бұрын
Mike this is amazing and blows my mind. I have a list of clients up to 490 and wanted to extract them by Status (Dormant, Inoperative and Active from a fact table and this video just sorts it out for me. Thanks
@excelisfun
8 жыл бұрын
Glad the video helps!
@douglaszulu6281
8 жыл бұрын
Hey Mike. I would like to get certification to be a Microsoft excel trainer. How do I do it? You can connect using skype (douglas.zulu) or email douglaszsam@gmail.com
@excelisfun
8 жыл бұрын
I do NOT know anything about certification. Sorry about that.
@josebetancourt5890
8 жыл бұрын
Its one of the best videos you have done! Because it's a very functional, it can be used into our job to accomplish sort kind of tasks. Thank you!
@excelisfun
8 жыл бұрын
You are welcome! Yes, it is useful for sure. Thanks for the Thumbs Up and Sub!
@rameshbankar100
4 жыл бұрын
very excellent video... i am just searching for this ....... thanks bro.
@gumgumbi
8 жыл бұрын
Exactly what I am looking for!!! This tip!!! Thank you so much :)
@excelisfun
8 жыл бұрын
You are welcome so much!
@victor_wang_1
8 жыл бұрын
Gosh, and all this time I was using INDIRECT to lock table references. I had no idea about that double bracket and colon trick! Thanks!
@excelisfun
8 жыл бұрын
Yes, Double Brackets would be better, especially with big Array Formulas.
@muhammadfathi3845
8 жыл бұрын
Stunning formulas!!!
@excelisfun
8 жыл бұрын
Glad you like them!
@meysamto2601
4 жыл бұрын
thank you very much thats great its very usefull and helpfull
@annettabogucka429
8 жыл бұрын
Thanks, for doing great job! This is a real game changer for me!
@excelisfun
8 жыл бұрын
Awesome! Love to hear that it is a game changer. Thanks for the Thumbs Up and Sub. If this is a game changer, you may love the book I wrote all about Array Formulas; "Ctrl + Shift + Enter: Mastering Excel Array Formulas".
@chriswebb4797
2 жыл бұрын
Hi Mike, great video, I think that the filter function might work too. Keep up the great work!
@nadermounir8228
3 жыл бұрын
Absolutely brilliant Mike 👏
@excelisfun
3 жыл бұрын
Old School Fun!!!!
@rrrprogram8667
7 жыл бұрын
This is routine to mention that you are fantastic
@williamarthur4801
Жыл бұрын
Greate Video, always learn something new, I shall have to work through it a few, times, out of interest I had a go on my own first and for the iINDEX used a similar formula for rows SMALL(IF(fSales[[#All],[Product]]=Quad!$B$1,ROW(fSales[[#All],[Product]])-3),ROW(1:1)) , but rather than dragging the formula, I just put a 0 for the columns so they spilled. I see this is a few years ago, (lifetime in excel) so maybe this option wasn't around then, lord knows if i can remember.
@mohamedchakroun4973
8 жыл бұрын
Absolutelay crazy video that spins the head
@excelisfun
8 жыл бұрын
Hopefully the crazy spin is okay...
@heeren.9
2 жыл бұрын
Excellent 👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻
@redhaakhund1271
3 жыл бұрын
Amazing👍👍👍👍👍, I have two lists of Employees and in both are the same names. If Employee 1 in List 1 worked on Thursday than on Table 2 the same Employee should work on Normal day such as Sunday, Monday, Tuesday, Wednesday. How to do this challenge. Please advise. Thanks and waiting your kindly reply.
@ezzatelkassar
7 жыл бұрын
Of course, we gonna like your video
@excelisfun
7 жыл бұрын
Thank you very much! : )
@cnwen
4 жыл бұрын
Thank you so much!!!
@jazzista1967
8 жыл бұрын
simply mind blowing!
@excelisfun
8 жыл бұрын
Glad you like it!
@marounsader318
8 жыл бұрын
thanks a lot waiting for the powerquery solution in order to automate huge data splitting great job
@excelisfun
8 жыл бұрын
Automating is done with VBA. I am not good with VBA, though... : ( Power Query will make it a bit easier than formulas, though...
@marounsader318
8 жыл бұрын
ya me too suck with vba i want to see where PQ can help and whats the result will be this would help a lot
@excelisfun
8 жыл бұрын
The Power Query Method is really cool... I will post a video on Friday... It takes a long time to make these 20 minute videos...
@marounsader318
8 жыл бұрын
ExcelIsFun im guge fan of ur work dear 20min, 1 hour, 3 hours no matter what its always informative and top notch
@excelisfun
8 жыл бұрын
Thank you for being a guge fan! I am glad that the videos help...
@pinaki1162
5 жыл бұрын
Hi Mike Thanks for an exceptionally useful video. You are great as usual. However, when I am practising in the downloaded exercise file, the formula is working fine for the first column but it is not automatically changing according to next column header. Why this is So?
@460s5
2 жыл бұрын
Thanks for this! What if we need to account for results that aren’t direct matches? Can the formula be adjusted to pull in all results that include the text in B1? Example: if we want to pull in all products under Yanaki and the source table has some labeled Yanaki2. How can we ensure we are pulling in all results that reference Yanaki?
@masterof
8 жыл бұрын
Very good video, thank you!!
@excelisfun
8 жыл бұрын
You are welcome!
@ronverheijen5407
8 жыл бұрын
Hey Mike, Very good one, this. Just wondering: is there a way to dump the possible product values into a list box, and then have the sheet name copy that product value (and perhaps generate as many sheets as there are product names)?
@excelisfun
8 жыл бұрын
Probably with VBA. I am not good with VBA, though... : (
LOVE THIS! One more thing though - how would you also have the array formula sort the sub tables by one (or more) of the column values (e.g., Date only OR Date then Price, etc?)
@excelisfun
8 жыл бұрын
That is a whole other level of complication. I have videos about that but it is very complicated and not often worth the added effort. In my Book Ctrl Shift Enter: Mastering Excel Array Formulas I cover sorting formulas in chapter 19, but chapters 15 up to 19 set the ground work. Here is a playlist of videos with all the videos from the book, including sorting formulas: kzitem.info/door/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci Also, Here is one video that shows how to create a formula for a sorted list: Excel Magic Trick 1314: Array Formula To Create Sorted Unique List with Mixed Data kzitem.info/news/bejne/qpCQtqKpi3ZqY5g But, as I said, thery are VERY complictaed. It is better to just use Power Query. Here is a video: Excel Magic Trick 1315: Create A Unique Sorted List with Power Query kzitem.info/news/bejne/w4assJuVn5OKi5w
@adamgoodrich80
8 жыл бұрын
Thanks for all the options! I'm just not a big fan of power query... I like everything to update instantly as soon as data changes (especially since a lot of my tables are on different sheets, etc.
@excelisfun
8 жыл бұрын
I agree: Formulas are awesome this way: Update Instantly, just like Magic : )
@ges05
3 жыл бұрын
Wonderful, Mike!! Thank you so very much! I would like very much to do the same but, per month. I.E.: get all records that fall under one criteria and in a date range (in January; in February; etc...) How can we wrap that date range as a second condition? Thank you very much. All the best.
@ges05
3 жыл бұрын
Got it, Mike. I'm extracting all records falling under 3 criterias: >= 1st day of date range I choose;
@chincc5185
7 жыл бұрын
Hi Mike, thanks for your video, Its better if we change the ROWS(A$5:A5... become ROW(A1) for both?
@excelisfun
7 жыл бұрын
No, no, no, no...!!!!!! In many of my videos and books I have shown the problem with that. If you insert a row above then all formulas stop working when you use ROW(A1)... I have been careful and vigilant to try and always teach efficient and effective methods : )
@shane708
2 жыл бұрын
Is there a simple way to do this in 2022? Maybe using some new formula?
@jonathancooper787
8 жыл бұрын
Awesome video.
@excelisfun
8 жыл бұрын
Glad you like it!
@MrPACO0508
6 жыл бұрын
sir good job.. but i have one question. how about if you have a two criteria?? pls help me sir
@vhc6600
2 жыл бұрын
I would prefer to have a helper column for the extracted records 1,2,3,4 etc which I would replace ROWS with a reference to
@iancockerline2305
7 жыл бұрын
This is terrible, I wanted to learn ONE thing! The video taught me dozens of things! That's just reckless teaching! Now I'm going to be the one in the office everyone comes to for excel help! Joking aside, this is a great teaching style. I love how you use the F9 feature to show the formula returns every-time I think I'm lost.
@stevennye5075
4 жыл бұрын
well done
@excel2473
8 жыл бұрын
Poor excel perfection, really enjoyed this one. But the power Query Solution will be much suitable for the normal excel user and big data sets are running much much faster. Thanks for this one
@excelisfun
8 жыл бұрын
I am not sure what you mean by "Poor excel perfection"? What do you mean?
@excelisfun
8 жыл бұрын
I agree about Power Query: it really transforms the way we can do things with large and big data. There is just no way that large array formulas can handle any data that starts to get big.
@excel2473
8 жыл бұрын
ExcelIsFun pure instead of poor :D spell check gone wrong...
@excelisfun
8 жыл бұрын
Poor fun!!!... I mean Pure Fun!! : )
@mikeburton8687
8 жыл бұрын
really awesome!
@excelisfun
8 жыл бұрын
Glad you like it!
@56457645754
6 жыл бұрын
Exactly at 10:35 can I use a function similar to LIKE so I can search for values inside fields that might contain other characters as well? So instead of =$B$1 can we do something like LIKE $B$1?
@74rocka
8 жыл бұрын
Fantastic. I have been trying to do this for work but because it is a legacy system (2003), how would I replace the aggregate function with the small function?
@excelisfun
8 жыл бұрын
For this video, something like this: =IF(ROWS(A$5:A5)>$B$2,"",INDEX(MasterTable!A$6:A$213,SMALL(IF(MasterTable!$B$6:$B$213=$B$1,ROW(MasterTable!$A$6:$A$213)-ROW(MasterTable!$A$5)),ROWS(A$5:A5)))) I have about 50 different videos on SMALL and INDEX. Here are a series of applicable ones: Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula kzitem.info/news/bejne/taZtr2mran-inII Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula kzitem.info/news/bejne/s2uQvXWfh3x9gaQ Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula kzitem.info/news/bejne/kmlowJimsXSeZIo
@excelisfun
8 жыл бұрын
Thank you are the Thumbs Up on all the videos and Sub!
@excelisfun
8 жыл бұрын
I just updated the downloadable Excel Workbook File with an example of how to do this with SMALL.
@74rocka
8 жыл бұрын
ExcelIsFun Thank you very much for your assistance. Educational, entertaining, responsive and generous with your time and knowledge. It is greatly appreciated.
@74rocka
8 жыл бұрын
ExcelIsFun Thumbs Up on all.
@yinkade
8 жыл бұрын
So good.
@excelisfun
8 жыл бұрын
Glad you like it!
@cinnemonn
6 жыл бұрын
Is there a master table row limit?
@entertainmentgalaxy971
7 жыл бұрын
Beautiful and nice effort. I think it can be done with pivot table m i right? Many thanks for sharing....
@excelisfun
7 жыл бұрын
Really? I am not sure how to do it with a PivotTable. PivotTable are for making aggregate calculations with conditions or criteria. In general PivotTables are not effective for extracting data. Although maybe you know a trick : )
@entertainmentgalaxy971
7 жыл бұрын
ExcelIsFun it worked like same u did with formula and pq. even net revenue column can be added with PT.
@excelisfun
7 жыл бұрын
Are you just dropping fields into Row Area?
@excelisfun
7 жыл бұрын
Tell me how you do it, especially if there are duplicates.
@entertainmentgalaxy971
7 жыл бұрын
ExcelIsFun yes u r right. and net revenue column can be created with analyze ribbon tab. and i got same result with this way as well so far.
@RAHULfromIND
3 жыл бұрын
Superb
@excelisfun
3 жыл бұрын
Glad you like it!
@addykz
8 жыл бұрын
My brain is turning to mush😂..thanks for this! I've wanted to do this many times at work but didn't know how to do it
@excelisfun
8 жыл бұрын
Glad the video helps!
@grouchPotato
7 жыл бұрын
In the video the criteria is the Product. What if a second criteria is required? The date to be exact. And I just want to pull data referring to "this product" only on "this month"? how would the formula go? my head hurts trying to figure it out myself update! figured it out!! injust need to eat. hahaha
@AlexIsFREEandRunning
3 жыл бұрын
That's exactly what I'm searching through the comments and videos for. Unlike you, I haven't hit the Eureka moment yet :(
@rameshbankar100
4 жыл бұрын
hello sir i typed the array formula but i am getting #Num! error whats my fault, please give solution..
@amycurry9237
7 жыл бұрын
How would I transfer blanks from master to sub tables in this formula? I am getting a date return of 1/0/1900
@ankitmittal8042
8 жыл бұрын
mind blowing could you please assist us in developing in knowlege for macro by uploading the vedios
@excelisfun
7 жыл бұрын
I am sorry, I am not good with VBA and so I do not make videos about that topic. You can try these awesome KZitem sites: kzitem.info/rock/bi5G5PjWBaQUFy7XU_O7yw or kzitem.info or kzitem.info
@ankitmittal8042
7 жыл бұрын
Thanks for the refrence sir
@excelisfun
7 жыл бұрын
You are welcome!
@kaushikshah6115
3 жыл бұрын
I like this but large data take too much time nd excell hangs so much...
@rinauong8956
6 жыл бұрын
I trying this way but it doesn't show . I don't know why .
@entertainmentgalaxy971
7 жыл бұрын
Hey there is one limitation, it cant be sorted like if A to Z or Z to A..... ?
@excelisfun
7 жыл бұрын
It could be sorted, but it is CRAZY!!! I have a few sorting formulas in my Ctrl + Shift + Enter book and video series. But it is almost always too much effort to extract and sort with formulas... Especially now that we have Power Query.
@bttarius
8 жыл бұрын
Hey Mike, ever since I upgraded to the latest build of excel in office 365, I have lost my power pivot function. It doesn't appear in the command add ins option list so I am unable to restore it. I have also uninstalled office and reinstalled it but the result is still the same. I have tried reaching out to Microsoft but that hasn't been forthcoming as well. I hope you can help or at least refer me to someone who can.
@excelisfun
8 жыл бұрын
I really can not help with the bad bad Microsoft behavior and marketing. It is just bad how they do it and it harms a lot of people. All I can say is that you need the correct version: "Office 356, ProPlus", then you have to fight Microsoft. I had to to a similar thing when I tried to get the right version. Also, watch out for retailers like Best Buy: they often don't know what they are talking about. A number of students in my classes and myself got the wrong advise about what version we needed. The bottom line is Microsoft is really hurting themselves and customers in how they market Power Pivot, and since we must have it (because it is such a great tool), we must fight them until we get it... : (
@alipezu
8 жыл бұрын
I am confused with array formula. Please explain what the array formula is? What I know about array formula is pressing Ctrl+Shift+Enter together rather than just Enter. but in this video you didn't press these three keys together after typing the formula. Is it still called an Array formula??
@excelisfun
8 жыл бұрын
I have hundreds of videos about this topic and have written a whole book about this. Here is one video that should help: Highline Excel 2016 Class 05: Excel Array Formulas: Comprehensive Lessons: 12 Examples kzitem.info/news/bejne/s3qGl6l8cHqSmYo You should watch the whole 52 minute video, but at the 10:00 minute mark I discuss how to decide when to use Ctrl + Shift + Enter. The short answer to your question is: The formula in the video is 100% an Array Formula. using Ctrl + Shift + Enter has noting to do with whether or not a formula is an Array Formula. Array Formulas are: 1) Formulas that contain operations that operate on an array of items rather than single items AND results in an array of answers. 2) Array Formulas sometimes result in a single answer and sometimes they result in an array of answers. 3) Whether or not an Array Formula is entered with Ctrl + Shift + Enter is determined by the function argument. For example, an array operation in the array argument of AGGREGATE can handle array operations without Ctrl + Shift + Enter, whereas an array operation in the logical_test argument of IF can NOT handle array operations without Ctrl + Shift + Enter.
@alipezu
8 жыл бұрын
ExcelIsFun Thank you so much for the explanation
@excelisfun
8 жыл бұрын
You are welcome!
@rameshbankar100
4 жыл бұрын
i have an error in countifs, when i type countifs(fs no icon of table comes there and i cant select the table header... please solve this
@fabiolarivas1430
3 жыл бұрын
It is the name of his table, fsales or something like that. Got to your master table and click on the design tab on the ribbon. From there on the far left corner there is your table name, you can rename based on what you want
@abdelrahmanramadanfadli2118
2 жыл бұрын
I have table with million rows. the split table is 41thousnad rows. dragging down the arrow this much will be a hassle. Any tip? after searching I found couple of things... Using this formula for huge data set is totally a suicide. AHAHA
@jufengsiau4638
3 жыл бұрын
Hi I keep getting number error #NUM! can you help me on this?
@simitar540
3 жыл бұрын
Same here :-( Looking at "Product" hitting F9 key all fileds are FALSE, but there is in fact, 2 hits. The result is a #NUM which is not truth. I would really appreciate your help on this. Thanks in advance.
@simitar540
3 жыл бұрын
Rectifying, it's all ok now. My fault, it was just a typo issue.
@noneyabeezwax8865
7 жыл бұрын
Your brain is scary 😊 in a great way!
@douglaszulu6281
8 жыл бұрын
Hey Mike. I was playing with the file and added a second criteria and this is what I arrived at with category as second criterion: =IF(ROWS(A$5:A5)>$B$2,"",INDEX(fSales[Date],AGGREGATE(15,6,(ROW(fSales[Date])-ROW(fSales[#Headers])/((fSales[[Product]:[Product]]=$B$1)*(fSales[[Category]:[Category]]=$E$1))),ROWS(A$5:A5)))) Is there another way without multiplying the conditions?
@excelisfun
8 жыл бұрын
That is a fine way to do it!
@douglaszulu6281
8 жыл бұрын
Thanks Mike for the thumbs up. Took me a while to catch the concept of the condition in the aggregate. Now am having a challenge when I use it to extract a condition where there is a column of both negative and positive. I would like to extract the negatives alone and the positive values separate.
@excelisfun
8 жыл бұрын
I think that is possible: just make the condition something like: ArrayOfNumbers>=0 for positive and zero, ArrayOfNumbers
Пікірлер: 164