Excel 2010 Magic Trick 818: Extract Records For Each Country To A New Sheet With Formula

welcome to Excel magic trick number 818 if you want to download this workbook a so nice trick 8:15 to 818 click on the link below the video in our last video 817 we had this data set and our ultimate goal was to extract the Singapore records to a new sheet the malaysia records - a new sheet the Philippines records - a new sheet and we've talked about how in magic trick 812 we use pivot table show report filter pages but that's not dynamic if you start adding records to the table it's not dynamic so our goal is to extract each one of these sets of records to its own sheet and have it all be dynamic last video 817 we added a helper column here and then for data validation we extracted a unique list of items and built our data validation drop-down list right here alright so in this video we're gonna actually extract the records so I'm gonna come back over here and highlight all the columns I don't need the helper column I'm gonna click there ctrl shift right arrow and then holding shift I back arrow 1 and copy just to get that and ctrl V I hope that doesn't go all the way over yeah so we left enough room all right and so now our goal is to put one formula here that we'll extract now that's going to be an array formula but it's the idea ultimately is that it's all dynamic once we have our little table we're gonna copy this sheet over a bunch of times change each drop down here and all the records will be individually distributed across multiple sheets alright now the first thing is we if we select an item here we need to know how many they are so I'm gonna say count and in our last video we saw how this table is an actual official table we control T and converted it to a table and we were using table nomenclature so now I need to look through this column well since it's a table this column is called country and the table is called table two if you go up to the table tools design you can see there our table name table too so watch this we're gonna use for a table formula nomenclature alright now I'm gonna add some green here this is gonna be a format I'm gonna do the same right there alright so we're gonna use count if a simple account if and the range I'm gonna type table tab and then a square bracket this is the table formula nomenclature and as soon as you type a square bracket it brings up a list of field name so I'm going to double click close square bracket if only I could type comma and then the criteria is gonna be right here control enter right so there's 13 now I'm gonna come here and the formula is going to go way down but it needs to turn off when it gets past row 13 so I'm gonna say equals if rows if only I could rows and I'm sitting in a 5 we talked about this in the last via this is an awesome way to increment numbers in a formula I cannot type at all today right so it'll go 1 2 3 4 5 as we copy down anytime that's greater than this f4 to lock it then the true what do I want to show well pass row 13 I want to show nothing so I put double quote otherwise and here's our lookup form and now this is gonna be a big array formula I'm gonna use index now I'm gonna actually have to build since I can't use mixed cell references and I want mixed cell references I'm gonna have to build this and even though we're gonna not use formula nomenclature here it'll still be dynamic so I'm gonna go over here and look right now it's the first column is a2 and the the first cell is a2 and the last cell is a 405 so I've already now clicked over there some and I have the sheet name so that's the sheet name and I'm going to type a dollar sign I've already forgotten is at five yeah 5 colon a dollar sign for five I'm gonna go check that I'm gonna come to the beginning of this formula and type a space just to suspend it go over here so it was 405 - a - so I'm gonna unsuspended of that space so it's two right here all right now even though it's not using formula nomenclature that 405 will expand as we create records over in that other table all right already comma and the reason we put dollar sign in front of just the numbers but not the columns is because when this formula copies over boom boom boom boom and I can already say I have a mistake it's not I got the wrong sheet so I'm gonna have to suspend this and do something really tricky come down here double click the sheet name copy escape click right here double click and put it back in - and inside of those little single apostrophe so I'm gonna highlight that and ctrl V alright so now it's looking over there all right now the row number we're gonna we have multiple matches for Singapore and so when I use the small and then inside to create the array of row numbers I'm gonna say if and the logical test now I want to look through what this the country column and ask is anything in that column equal to Singapore so I'm gonna type ta tab square bracket get my country down arrow tab close square brackets anytime that's equal to this and I'm gonna hit f4 and lock in all directions by the way that is lock that country right there comma any time that's true then what do I want I want row because I'm ultimately interested in some rows and I'm just gonna use this little thing right here copy now that'll give me just row numbers like 2 2 4 or 5 but I really want the numbers 1 2 3 4 5 so I'm gonna have to subtract from this row and I'm actually gonna click back on this sheet and click in the first cell on that column you can see it puts it in there I'm gonna hit f4 to lock it close parenthesis and then a comma a comma I'm gonna come back over to this sheet now what it does is when I type a comma and come back over the sheet it puts that nomenclature in but I'm just gonna backspace out of that so that right here is not correct the value of true right now it give me rows two to four oh five minus Row two so give me 0 it give me the number 0 all the way to 404 so I need to add one more that is our tricky way of getting our row numbers into the small so I'm gonna close parentheses I do not need this final false close parenthesis the array there it is it's gonna give us a bunch of row numbers comma and we need the first second third as we go down but not past thirteen so I'm gonna use this same little bit right here copy into the small closed parenthesis alright so that is our row number the amazing thing about this is right now it should give us one if I hit f9 gives us one control-z but when I copy down and give me two three four for all the singapore's all right so that's the row number I can simply close parentheses on that the value if false is our index so I close parenthesis on that and this is an array formula so I'm gonna have to hold ctrl shift and enter then I'm gonna copy it over and then copy it down we are gonna get some isn't you know wait you need to copy down past the the largest number you think he'll ever expect now those zeros are there because there's blanks in the data set I'm not going to do that I could amend this formula further with another if that said you know if this is 0 but I'm not going to or now let's try it let's change it to Malaysia and sure enough all the Malaysia records now the big trick here since this is a magic trick 8 17 and 18 if I go over here and add a record and add a I'll come down to the bottom and add a new country everything should be dynamic so I'm just gonna test it as soon as you type it at the bottom of the table it adds new record and I'm gonna type good good good go that's my new country go back over here I'm first gonna check over here sure enough goo gaga is there and it's increased its count to 13 that's from XL magic took 817 and then this one I should come down to goo goo goo goo goo and at the bottom again we have some blanks if I select this it should work just fine now I'm gonna go back and delete this record because I don't want it we just test in it delete right now the final little piece to this and we can expand the columns and make it as big as we want right this is gonna be on each sheet and pulling things out and expanding and this is just Singapore but I want to copy this sheet over so watch this I'm gonna hold control point to this sheet right here and click and drag and then click do that again and do that again that's making new sheets and each time they're exactly the same but watch this now I can come to the e to change this one to Malaysia the III changed to this one to the Philippines v4 change this one to Vietnam and now the whole idea behind this is it's totally dynamic so as we're adding records here every single little sub table using these wild formulas will update now I can see I don't have the right you know I don't have the right format I'm gonna well okay so I could fix that format here I was not being observant this whole column should be control one date right so we verify that everything's correct here you know and expand the columns make it all perfect before we then copy it over oh wow that was a wild couple of videos alright we'll see you next video

Channel: ExcelIsFun

Views: 24,106

Rating: 4.9144387 out of 5

Keywords: Excel, 2007, 2010, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Slaying, Dragons, Array, formula, to, Extract, Records, For, Each, Country, To, New, Sheet, With, Formula, INDEX, MATCH, SMALL, IF, ROWS, Table, Nomenclature

Id: pHdftPxQsVE

Length: 10min 42sec (642 seconds)

Published: Sun Oct 09 2011