Excel Magic Trick 1346: Array Formula: Split Master Table into Sub Tables, Each Product on New Sheet

welcome to Excel magic trick number 1346 hey if you want to download this excel work like XO metric 1346 finished or the start file so you can follow along click on the link below the video wow we have an amazing video here we want to see how to split a master table into sub tables with each product on a new sheet now here's our master table date product region and so on and here's the product column what I really want to do is automatically from this table as I add new records I want to break this apart so that on the aspen sheet I have all the Aspen records carlota I have all the carlota records not only that but if I come down here and double click and change this to the word quad instantly I want all the records to be not for Aspen but for quad back to Aspen now back to this master table sheet I'm actually going to do two videos in the first one 13:46 we're gonna see how to do it with an array formula and then in 1347 we'll see how to do this with power query all right so we're gonna start with the array formula version now I'm gonna go to the start file here's the start file right here and here's our master table now i've already converted this to an excel table and named it under design properties the name of the table is f sales now we're gonna need to reference multiple columns in our formula and i want to give you a preview of how the table formula nomenclature works when we're referencing columns i'm gonna highlight the top cell in the product column control shift down arrow to jump down to the bottom ctrl backspace to jump back to the active cell our table formula nomenclature will always have table name and field name in square brackets but watch this if i enter this and forget the error if i copy it down i want you to notice that it's locked on the product call but if I were to copy this to the side product changes to region so we're gonna have to be aware of that behavior when we build our formulas over on the next sheet alright let's go over to Aspen our first task is to extract in this cell right here the product name from the sheet tab now we can start to build the formula by using the cell function cell function is a great function that lets us extract all sorts of different information we want simply the file name now cell does some funny things sometimes so you need to lock it down on this sheet by putting a 1 now I've done other videos about what happens if you don't do this but for us that's what we need to do when I control enter there's the entire file name now notice at the very end is the sheet name and it will always follow a closed square bracket so I'm going to use the replace function to replace everything from the first character all the way to that close square bracket and replace it with nothing then I'll be left with just the Aspen so f2 I'm gonna use a replace there's the old text and actually I need to copy this because I'm gonna have to use it twice in this formula ctrl C now remember we're replacing so comma this start number one I want to start at character one comma and I need to go how many characters all the way to that close square bracket so I search for the position of the fine text please find close square bracket comma within what text control V so now search when I close parenthesis it's sitting in number of characters so if I click on it and hit f9 to evaluate it real place will go from 1 to 84 characters in that filename control Z and comma the new text it's gonna put in its place as double quote double quote now technically that's a zero length texturing but for us it'll just nothing close parentheses and enter you gotta be kidding me look at that it got the name if I double-click and call this quad to check it and enter sure enough it's working fine I'm gonna type Aspen and enter now our next goal before we create our formulas for looking up Aspen records and return them we actually need to count from our master table how many records are for the product Aspen if we look back over here we simply need to look through the product column and count all the Aspen's we can use the count ifs function now the criteria range we need the entire product column now instead of going back to the sheet and highlighting the entire product column table formula nomenclature is awesome I simply type FS and look at that the screen tip even gives me an icon that says hey this is a table tab then I type a square bracket and look at that not only do I have the option for all the field names or column headers I also have a bunch of other very useful table elements now we want products so I can double click or down arrow and tab close square bracket now notice it does not show up in blue and we're on a different sheet than the actual table now I type a comma and my criteria is up arrow B 1 Aspen when I hit enter 36 records over there for Aspen now when we think about formulas to extract records like this notice the look of value is Aspen and there's 36 duplicates normal lookup functions cannot handle duplicates that's why we have to switch over to an array formula now I want to look at the index function that's the lookup function we're going to use an array that's gonna be all the items we potentially want to go look up but it's the row number or relative position of the item you want to extract now remember escape if we go look here the row no our argument as we copy the index down would first need a four then a seven then a nine as the relative positions not the 14 12 and nine so what we're gonna have to do in our formula is for each column date product regent for this record it's gonna have to know please always go and get the fourth item in the date column fourth item in product region and so on if we did it manually it would look like this this whole row four index for the row number would need a four this whole row would need a seven and this whole row would need a nine no problem we can do this with an array for now the trick is we're gonna have to create that entire array of matching relative positions for Aspen and then as the formula copies down we're going to need to extract the first smallest which will be four second smallest which will be seven nine and so on now we would like to use the small function the small function allows us to extract the first smallest second smallest third smallest as we copy the formula down but I don't want to use small I want to use a function that came in Excel 2010 aggregate now aggregates awesome because it will allow us to use the small function if in the first argument function number we put a fifteen now the reason that we would use aggregate 15 instead of smalls because when we get to comma comma the array argument it handles array operations without any special keystroke all right so we picked function 15 for small options we definitely want ignore errors because our resultant array will contain errors and we need to say hey ignore those errors comma and this is the magic argument we're going to create our entire array of relative positions now we're going to start off by using the row function and we're going to look through the date column so I type FS tab square bracket tab for date close square bracket close parenthese now Rho function simply looks at that entire column there and gives you the actual row number so if I hit f9 to evaluate that 6 7 8 9 remember 9 is not what we want we need a 4 right there so control Z now remember the first record is 6 so if I subtract row again but on a single item F sales square bracket and I'm going to tell it to look at the headers now remember the header is an element in that table that always sits exactly one row above 6 which will give us a 5 close square bracket close parenthesis if we highlight just row F 9 we can see that gives us a 5 now remember this had 6 7 8 well 6 minus 5 is 1 7 minus 5 is 2 and so on control-z now I need to close off the orange parentheses that whole array right there if we f9 to evaluate it there is every relative position 1 2 3 4 all the way to 187 notice we need to eliminate or filter out all of the 1 2 3 and just keep the 4 than just the 7 than just the 9 and so on control-z well I can do that by dividing that whole numerator by the product column F sales tab open square bracket P tab close square bracket now I definitely need to ask the question of the product column are any of you equal to Aspen now what's this that's a normal cell reference so I can lock it with the f4 key but that range needs to be locked on product as we copy over and down as we saw at the beginning of the video it would be only locked as we copy down if I copy this to the side it'll move to the next column region so I need some way to lock this if I hit the f4 key it just doesn't work for table formula nomenclature so it's kind of clunky but you have to put open square bracket and then very carefully : open square bracket product and I'm gonna hit tab and then close square bracket close square bracket that crazy little thing right there means table formula nomenclature locked no matter where I copy it on the product column alright so you already closed parenthesis now this little bit will just give me if I f9 trues and falses and notice there's a true in the fourth position in the seventh position because this is in the denominator falses will be zeros and truths will be one when we run that division operator control Z now watch this this is totally magic if I click the entire array meaning numerator division denominator and f9 there's my filtered list four seven nine the six will tell the formula to avoid the divided by zero error and the fifteen meaning small will extract the first one then the second one then the third one as we copy the formula down control Z the last argument you could see it right here for small comma is K remember I need the first smallest for the first row second smallest third smallest and so on well I'm gonna have to create a formula element right here that as I copy down gives me their numbers one two three and so on we use not row but rows now I have to know which cell I'm sitting in I'm sitting in a five so I'm gonna type a dollar sign 5 colon a 5 close parenthesis now what's so cool about roses it'll count how many rows there are in the range 5 to 5 as one but because there's no dollar sign right there it'll move to 6 and then 7 and then 8 as I copy it down so it'll give me the numbers 1 2 3 4 as I copy down now let's close a Greek it off and the reason that we used Agra instead of small is because that's a big array formula if we'd use small we'd have to use some special keystrokes but I can just enter it I used control enter and watch this when I copy it over and down that is the magic of this array formula for extracting records that have multiple matching rows now I can simply come to the top cell f2 that's delivering the relative position for index so I put index remember I need to extract date product region and so on so in the array F sales tab square bracket date is the first column close square bracket comma now remember how table formula nomenclature works that date will move to product and then region as we copy it to the side across the columns but as I copy down it will be locked now I'm very carefully gonna come all the way to the end close parentheses control parenthesis and I get an errand actually this happened to me earlier when I hit tab on the date it got rid of aggregate so watch this I'm gonna have to type aggregate again I could come to row number and make sure I got it all there yes indeed control enter that's a date I'll worry about that in just a second let's copy it to the side and two things I do not need this last revenue column I mistakenly copied it over too far so I just deleted and then right here before I copy it down control one and I'm gonna select date click OK now I can highlight this row and copy it down I'm dragging dragging dragging down to some point past which there will be no more record so I actually have to know my data set now we get numb errors down here which will fix in just a moment but I want you to notice something that these are 36 records right all the way to the top and past 36 I don't want this error to show up what I do not want to do with array formulas that extract records is I do not want to use the in air and the reason why is because that means the array formula will be running in every single cell include pastro 36 now array formulas sometimes take a long time to calculate especially if you have a lot of them in your workbook and we are gonna have a lot of them so don't use it fair we're gonna use the if function and pick an alternative logical test a logical test different than testing to see whether the formula errors out and no problem I'm for the logical test in the test hey when the rows function which is our number incrementer as I copy down 1 2 3 4 when you are greater than 36 and then I'm gonna lock it with the f4 key whenever that's the case then please instead of running the array formula value if true I'm gonna put double-quote double-quote now that's a zero length text string that'll just be dumped in the cell all their y's the value is false will be our big array phone and that array formula will only run for us in the first 36 rows I'm very carefully gonna come to the end close parentheses control enter now when I copy this to the side it's gonna copy that number formatting so I'm immediately gonna point to the smart tag and say fill without formatting then I'm going to double click and send it down and sure enough when I scroll down look at that now remember if error we would have had to run the array formula down here but it never gets to that array formula it's just dumping that in after it runs the logical test control down arrow that is amazing control home we have to test this let's come down here double click Aspen and type quad and enter look at that that is amazing I'm gonna change it back to Aspen and enter now I want to calculate revenue and in the next video as we mentioned for power curry we'll actually get to do all the data extraction revenue formula calculation all in the power query window but here we've extracted the actual records and now we make our formula I need to take units times price times 1 minus the discount annum and use the round function in the number argument and say hey give me units times price and I don't want to say times discount because that would just give me the amount of the discount so I say hey in parentheses one which is the entire amount minus whatever the discount is close parenthesis now comma these are pennies so I'm rounding to the penny so I put it too close parentheses control enter and I'm going to use the keyboard control shift for to apply currency and then double click and send it down control down arrow oops I'm getting a value error and that comes from the fact that I'm trying to multiply those zero length text rings and multiplication is math and they don't like text now there's a few ways we can fix this but I'm going to take advantage of the fact that I know there's zero length text rings there so control up arrow top cell f2 I'm gonna say if function if one cell to my left if you're equal to double quote double quote if that's the case the value of two I'm going to put double quote double quote otherwise please run the round calculation close parentheses control enter double click and send it down and boom there it's working now let's copy the sheet over and instead of right click copy and then clicking a bunch of things watch this if I click on the sheet with my mouse and drag it up notice there's a piece of paper right there and a black triangle pointing down but if instead of simply holding the mouse I also hold the control key watch what happens now there's a little plus right there on the piece of paper and that plus means I am copying now it's very important I have both control and my mouse held down at the same time I let go of the mouse first and instantly it copies this sheet now I double click and call this quad enter hold control click drag I see the plus I let go of my mouse double click I'm going to type Carlo ctrl-click drag let go of the click double-click I'm gonna call this sunshine and enter hole control click drag let go of the click double-click and I'll call this your Nike hopefully I spelled it right that is amazing now let's test all of this and we you know we can go down to the bottom of the table and see it's all working well let's go back over here and off to the side I've added some new records and I'm going to copy this because the idea is that this is supposed to be dynamic I'm going to copy these and I'm going to add them to the bottom of the table control down arrow the way the excel table works is if you paste something here and down it'll immediately become incorporated control V and look at that when we go to our aspen sheet we thinner have more than 36 and sure enough we do 39 let go down to the bottom that is amazing the quad similarly has 43 and so on alright that's a couple of pretty wild formulas we saw replace and sell and search to get the actual sheet named countifs and this awesome array formula I'm not with iferror but with if now these are formulas so the one advantage to formulas over our next video when we see power query as you saw everything automatically updated immediately I didn't have to refresh anything the disadvantage to formulas like this is array formulas especially when you get a lot of them in a workbook looking at huge ranges really slow down calculation time alright so control home on the master sheet we saw array formula stay tuned for 1347 we'll see an awesome solution using power query alright we'll see you next video

Channel: ExcelIsFun

Views: 39,227

Rating: 4.9623232 out of 5

Keywords: Excel, Microsoft Excel, Highline College, Mike Girvin, excelisfun, Michael Girvin, Mike excelisfun Girvin, Excel Magic Trick, Array Formulas, Ctrl Shift Enter Mastering Excel Array Formulas, Sub Tables From Master Table, Sub Tables on Each Excel Sheet, Return Multiple Records from One Lookup Value, CELL function, REPLACE function, AGGREGATE function, INDEX function, Extract Records on Each Worksheet, Split Master Table into Sub Tables

Id: RL8dXcbiTUc

Length: 22min 4sec (1324 seconds)

Published: Wed Nov 16 2016