Excel Magic Trick 1346: Array Formula: Split Master Table into Sub Tables, Each Product on New Sheet
Vložit
- čas přidán 2. 08. 2024
- Download Files:
Start File: excelisfun.net/files/EMT1346S...
Finish File: excelisfun.net/files/EMT1346F...
See how to use formulas to extract records from a Master Table to Sub Tables on Each Excel Worksheets for each product in the Master Table:
1. (00:17) Introduction. Look at Dynamic End Result Solution.
2. (01:28) Table Formula Nomenclature (Structured References) and how they ract when they are copied.
3. (02:35) Formula to Extract Sheet Name. See the functions: CELL, REPLACE and SEARCH.
4. (04:42) Formula to Count the number of records in the Master Table for each product. See the function COUNTIFS.
5. (05:57) Array formula to extract records based on Criteria. See the functions: IF, ROWS, INDEX, AGGREGATE, ROW and a number of Array Operations.
6. (17:25) Add Formula to calculate total revenue.
7. (19:07) How to Quickly Copy Worksheets using a Keyboard and Mouse.
8. (20:25) Test Where Master Table and Sub Tables are Dynamic and will update when new records are added.
9. (21:06) Summary & Considerations for Advantages and Disadvantages to using Array Formulas.
Extract Product Records From Master Table to Multiple Sheets (Sub Tables). How to Retrieve Multiple Records from One Lookup Value. Return Multiple Records from One Lookup Value.
Other Similar Videos:
Excel Magic Trick 538: Dynamic Sub Tables Based On Master Sheet Array Formula & Drilling Through
Excel Magic Trick 1347: Power Query: Split Master Table into Sub Tables, Each Product on New Sheet
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.
If you have Microsoft 365, then this task is MUCH easier. Here is a video: czcams.com/video/dha1T7mrFbY/video.html
@@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.
@@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 : )
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!
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 👌 👍 👏
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!
You are welcome! Yes, it is useful for sure. Thanks for the Thumbs Up and Sub!
Mind. Blown. Beyond thanks!!
amazing mike thanku for your all unique topics share with us 💯💯💯💪💪💪💪💪💪💥💥💥🔥🔥🔥🔥🔥🔥👌
Yes, I do have a lot of different topics so all of our Team can have fun : ) : ) : ) Thanks for your amazing support, Vijay : ) : ) : ) : )
I don't what to tell you man. PURE GENIUS!
I don't know what is better - beautiful formula or the perfect explanation.
Both are awesome!!!
Thanks
You are welcome, 'O PQ Poet Bill Szysz!!!
Exactly what I am looking for!!! This tip!!! Thank you so much :)
You are welcome so much!
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.
Glad it helped : )
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.
Glad to help, Akshay!!!!
Hi Mike, great video, I think that the filter function might work too. Keep up the great work!
You and Sal Khan are pioneers and your work will live long after you.
Thank you for the kind words! : )
Stunning formulas!!!
Glad you like them!
Thanks, for doing great job! This is a real game changer for me!
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".
This is one of the most ingenious and clever ways to use Excel, kudos!
Glad this video helps, Enrique!!! Thanks for the support with your comment, thumbs up and your Sub : )
Unbelievable! I never imagined one could do such in excel without VBA codes!!
Thank you!!!! Thank you!!! Thank you!!!! This saved me hours of work today and in the future
You are welcome, welcome, welcome!!!
Great video - very relevant for accountant who needs to extract data all the time ....cant wait for the Power Query video ...Thanks.
You are welcome, fellow Accountant!
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?
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!
Yes, Double Brackets would be better, especially with big Array Formulas.
very excellent video... i am just searching for this ....... thanks bro.
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!
Yes, the Old School is long and old ; )
simply mind blowing!
Glad you like it!
thank you very much thats great its very usefull and helpfull
This is routine to mention that you are fantastic
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
Glad the video helps!
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
I do NOT know anything about certification. Sorry about that.
Absolutely brilliant Mike 👏
Old School Fun!!!!
Thank you so much!!!
awesome, awesome awesome.... Respectfully.... thank you...
You are welcome!
The infamous array formulas...love it. A quick easy approach is to create a pivot table and use the option Show Report Filter Pages
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.
I totally agree with you said. Thanks MG!!
Excellent 👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻
mind = blown...thanks so much
You are welcome so much!
slight correction...=aggregate(mind[blown],15,6)
Awesome video.
Glad you like it!
Very good video, thank you!!
You are welcome!
well done
Absolutelay crazy video that spins the head
Hopefully the crazy spin is okay...
Of course, we gonna like your video
Thank you very much! : )
really awesome!
Glad you like it!
So good.
Glad you like it!
Superb
Glad you like it!
thanks a lot waiting for the powerquery solution in order to automate huge data splitting
great job
Automating is done with VBA. I am not good with VBA, though... : (
Power Query will make it a bit easier than formulas, though...
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
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...
ExcelIsFun im guge fan of ur work dear 20min, 1 hour, 3 hours no matter what its always informative and top notch
Thank you for being a guge fan! I am glad that the videos help...
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?
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?
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.
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
Glad the video helps!
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.
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)?
Probably with VBA. I am not good with VBA, though... : (
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.
Got it, Mike.
I'm extracting all records falling under 3 criterias: >= 1st day of date range I choose;
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?)
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:
czcams.com/play/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci.html
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
czcams.com/video/IZZPnsRD90c/video.html
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
czcams.com/video/bPvJg_faYXg/video.html
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.
I agree: Formulas are awesome this way: Update Instantly, just like Magic : )
Hi Mike, thanks for your video, Its better if we change the ROWS(A$5:A5... become ROW(A1) for both?
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 : )
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.
sir good job.. but i have one question. how about if you have a two criteria?? pls help me sir
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
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?
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
I am not sure what you mean by "Poor excel perfection"? What do you mean?
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.
ExcelIsFun pure instead of poor :D spell check gone wrong...
Poor fun!!!... I mean Pure Fun!! : )
Is there a master table row limit?
How would I transfer blanks from master to sub tables in this formula? I am getting a date return of 1/0/1900
Is there a simple way to do this in 2022? Maybe using some new formula?
mind blowing
could you please assist us in developing in knowlege for macro by uploading the vedios
I am sorry, I am not good with VBA and so I do not make videos about that topic. You can try these awesome CZcams sites:
czcams.com/channels/bi5G5PjWBaQUFy7XU_O7yw.html
or
czcams.com/users/familycomputerclub
or
czcams.com/users/ExcelVbaIsFun
Thanks for the refrence sir
You are welcome!
Your brain is scary 😊 in a great way!
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?
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
czcams.com/video/Tp7I5u1MqiM/video.html
Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula
czcams.com/video/R5ZWAiNJLNo/video.html
Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula
czcams.com/video/132ZdpxBm1U/video.html
Thank you are the Thumbs Up on all the videos and Sub!
I just updated the downloadable Excel Workbook File with an example of how to do this with SMALL.
ExcelIsFun Thank you very much for your assistance. Educational, entertaining, responsive and generous with your time and knowledge. It is greatly appreciated.
ExcelIsFun Thumbs Up on all.
Beautiful and nice effort. I think it can be done with pivot table m i right?
Many thanks for sharing....
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 : )
ExcelIsFun it worked like same u did with formula and pq. even net revenue column can be added with PT.
Are you just dropping fields into Row Area?
Tell me how you do it, especially if there are duplicates.
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.
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
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
I trying this way but it doesn't show . I don't know why .
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
That's exactly what I'm searching through the comments and videos for. Unlike you, I haven't hit the Eureka moment yet :(
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.
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... : (
hello sir i typed the array formula but i am getting #Num! error whats my fault, please give solution..
Hey there is one limitation, it cant be sorted like if A to Z or Z to A..... ?
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.
1:42
Thanks for your support, Sir!!!!
Hi I keep getting number error #NUM! can you help me on this?
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.
Rectifying, it's all ok now. My fault, it was just a typo issue.
I like this but large data take too much time nd excell hangs so much...
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??
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
czcams.com/video/RDP1uF7HafU/video.html
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.
ExcelIsFun Thank you so much for the explanation
You are welcome!
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
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?
That is a fine way to do it!
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.
I think that is possible: just make the condition something like: ArrayOfNumbers>=0 for positive and zero, ArrayOfNumbers