Combine Multiple Google Sheets (Workbooks) to Master Data File

okay so in this video I'm gonna show you how I can combine multiple workbooks Google sheets however you want to call them to a master so I have this separate Google sheets here's one so it has some data then we have the second one it has some other data and then we have this third partial that has some more data I want to have all of these combined in a new spreadsheet so I'm gonna start by first just grabbing the columns and copy pasting them so that should be pretty simple and easy copy and paste here we go now we need to combine all of those in this what we're gonna call the Master File so the first thing we need to do we need to make sure we have access to all of those three worksheets so what we need to do for that we need to use import range function so I'm going to start by typing equals import range and the first argument in this function is the spreadsheet URL or ID so it has to go as text in quotes so I'm gonna start double quotes and in between dos I'm gonna go to one of these I'll start with this first one so I'm gonna go to this one and copy this link and go back and paste it right here now you could keep this link if you want but I hate having this long URL so the alternative is to just keep the ID and the ID is starting from this D slash so I'm gonna remove all of this and then after the slash all of this we can remove that too so that's my first spreadsheet ID so I'm gonna do comma and then in quotes you have to do the range of data you're trying to get from this particular spreadsheet now if I go to that first partial for a second the worksheet name here is called sales and data range starts from a2 through G and I'm gonna go all the way down so a2 through G and sales worksheet so I'm gonna go here and do sales exclamation sign and then a2 colon G & G because I want to go all the way down if you want to limit this to let's say row 50 you could do this I'm not gonna do that just G and I'm gonna go ahead and hit enter now what's gonna happen it's gonna give me this reference error because you need to make sure you allow access to this so I'm gonna click this and you can see how that actually works now I have the data in this other spreadsheet so now I need to make sure that I can allow access the same way to all of the other spreadsheets I'm going to use so what I'm gonna do here I'm just gonna copy this formula without the equal sign hit escape and paste it over here someplace on the right so it doesn't get on a way and then I'm gonna go to my second spreadsheet and it's still gonna be the same C sales and the data range is still a through G so that's fine I just have to make sure I get the new ID so I'm gonna copy this go back to this one and open my formula and replace this with my other spreadsheet and again you could just keep it exactly like this but I'm going to just remove this rest then keep the ID now he'd enter and that should again give me the reference error and then I can click on that and allow access so now I gave access to two of those now again I'm gonna just copy this without the equal sign hit escape go back and face this here on the right someplace and then repeat this for the third spreadsheet and if you have ten you have to do this ten times unfortunately so I'm gonna go to third partial file copy this link go back to this and again repeat this process and I got that third one so I have three partials I'm actually gonna go ahead and move this over to the right to just copy this it escaped and paste it right in here so I have this three formulas now so I'm gonna need to use those in just a second so what I'm gonna do here I'm gonna remove this and I'm gonna start a new formula I'm gonna start equal sign and open a right bracket and in this array brackets I basically have to use some of those formulas I had on the right so if I hit enter now I'm gonna get an error because this is not a valid formula I'm just gonna close this bracket for now and hit enter and that's still not a valid formula but I need to copy and paste the rest so I'm gonna copy this first import range function go back to this and then inside of this brackets I'm just gonna paste that first one I'm gonna hit enter that's the first one that's fine I'm gonna go here and copy the second import range function that's by the way here on the right I can just do command C or ctrl C to copy that then I'm gonna go back to this formula and what I'm gonna do I'm gonna do a semicolon to go to the next row down and paste the second formula now to keep this clean what I'm gonna do instead of just pasting it here I'm gonna do alt enter so if you're a Mac user and you don't know what alt is that's pretty much the option key so that will basically just send this to the next line down and I can do this again see and I'm gonna paste it here just to keep this organized on separate lines you don't have to do this but I'm gonna do it like this I'm gonna also do alt enter here to also move this down a couple of spaces just to align all of this so now I have these two separated by the semicolon I'm gonna hit enter I am going to make this formula bar a little bigger so you can see what's going on here so that's my formula so far so I need to add now the third one again separated by a semicolon again so copy this third one go back to this and then to semicolon here and then again alt-enter goes to the next line a couple of spaces and paste I'm doing this so it's a line so I can see what's going on with this alt-enter stuff so I'm gonna hit enter again to apply this formula and you may get a value error sometimes because it's expanding the range but then it will work anyways so now I should have those combined in a way so if I scroll down see it goes to line 16 which is basically this now then we have some blanks here which will be probably for awhile and it really depends on how many blanks you have for example if I look here see there's a thousand lines so that means in my master now I'm gonna have a thousand lines blank before I get to the second one there it is so these are all of them with bunch of blanks in addition to just these so we want to get rid of those blanks so what I'm gonna do here I'm gonna pick a column so let's say I know that in this date column there's always gonna be some information in all of these spreadsheets so I'm gonna pick that column for me that's the first column from my data what I'm going to do here I'm gonna go back to this and I'm gonna put this entire thing inside of a query function that accepts data as the first argument which is going to be this entire array of combined stuff and then after that I'm gonna do a comma I'm gonna do alt enter again to move to the next line so I keep this separate and here the next thing is the actual statement so we'll do it in quotations so we're gonna do select which columns we want to display so I'm gonna do star for all columns and then I'm gonna add up we're close this allows you basically to filter your data so I want to make sure that I filter the first column to not have blanks in that column so I'm gonna say we're call one is not equal to two single quotes meaning in this case blank comma and then the last argument is how many head of rows we have in our data not in here now if you remember when I was selecting data I started from 8 - I did not select headers we don't want headers in this case so 0 for header rows I'm gonna hit enter so it says I got an empty output so apparently there's double quotes it didn't accept that so we probably have to say is not null instead for our blanks so I'm gonna do that and hit enter and now let's go check out what happens so you can see how all the blanks are gone and now I have my combined data so one thing to mention here this column is case-sensitive it has to be uppercase C so if you're gonna use column 3 it's gonna be column 3 just make sure you do uppercase C now this is one option to do this but you may end up running out of array limitation because of this whole blank situation if you end up having errors with this I'm gonna show you a different method we can do so we can pre-filter our data and then combine instead of combined and then filter like we did here so let me show you how we can do that I'm gonna add another worksheet here so again copy-paste column names I will also just copy paste those import range functions so that I don't have to rebuild the whole thing all over again it's still gonna be the same import range function to bring them over now I want to make sure I can actually filter that range and get a filter drink so it doesn't give me all the blanks together with the whole thing so what I'm gonna do I'm gonna do this I'm gonna go back to this I did copy that first import range so I'm gonna go here and do a filter function first and in this filter function the first argument is the range and the range is gonna be that range we're getting from import range so I'm gonna paste it right in here comma and that moves us to the next argument the second argument is the condition so the condition is we need to check that the column is not blank so again I want to check the first column in my data remember the states so that's column a so that's also coming from that other spreadsheet which is this one so I'm just gonna paste that same formula and say it's not equal to which is this less than greater than sign double quotes meaning blank and close parenthesis now because it was getting too long this sends me to the next line but this whole thing is actually one line there is no line break here okay now you want to make sure that just the first column is not blank in my case because I'm trusting it's the dates column not the whole a2 through G that's not gonna work so I'm gonna change this to a two through a so I'm gonna hit enter so again for a second that was thinking about it but here we go it gave me that range of data now I'm gonna repeat the same thing for every single other one right below this so I'm gonna copy this one and go here right below a little bit and do another filter function and I can do this because there are no blanks anymore here we already pre filtered it the data actually ends here so again the range is gonna be that second spreadsheet comma and in that second spreadsheet again same thing I'm gonna go and change this to the column I want to trust so column a and I want to make sure that's not equal to double quotes so I'm gonna hit enter so it says filter has mismatched range sizes okay let me go back and see what's mismatched about this this parenthesis is not in the right place this needs to go and we need to close import range function here right before so this is not blank so that should be accurate now there is so now we got the second one we need to repeat this again for the third formula so I'm gonna copy this import range scroll down and do another filter function so again the first one is the range comma then I need the condition which is gonna be the range but it's gonna go from a two through a now before I do that I want to finish this formula so I don't mess it up again so I'm gonna say not equal to double quotes and closed parenthesis and then I can go back and change this to a two through a if it's column D you're trusting it will be D two through D so there we go I'm gonna hit enter that should be the third formula from the third range now I got three ranges now the good thing about this is because we're grabbing those ranges and we're filtering them so the resulting range in this case is just this rows so now we're dealing with less data so now what I can do I can combine these three filter functions together so what I'm gonna do I'm gonna take the second one first and I'm gonna copy this halting this whole filter without the equal sign and I'm gonna delete that formula from here because I'm gonna need this room below to actually get the results so I'm gonna go back to this first filter function and I'm gonna convert this to an erase so I'm gonna start by again doing a curly bracket here I'll do an alt enter to move this to the next line this is the first formula so I'm gonna do a semicolon and alt enter again and apparently I had to do it twice to make it work and I'm gonna do paste and then I need to close my curly bracket now while I'm at this I'll add a couple of lines here alt enter again here and then now it's adding this blank here so it's just weird how this editor works so we have two filter functions now that should be combined so if I'd enter that should combine the first two ranges here see up to line 31 now if I scroll down I have this next filter function now so I'm gonna repeat this I'm gonna open this copy filter function without equal sign get rid of this and then I need to go back to my first formula and again just add this so again semicolon to add a new one alt enter to move to the next line and control-v or command-v to paste so now I have the third filter function I'm gonna hit enter so now I should have all three of them combined in one master file see 39 lines total I'm gonna remove all of this from here 39 lines let's go check this out so we got what's 16 here then 15 here actually this was 15 to 16 that's the headers so 15 15 that's 30 and then we got 8 so that's 38 records total so here we got 39 lines and the first one is headers so that's correct now let's also test what happens when we add data so let's start with the third one that's gonna be the easiest one it's this one if I go back see that updates and now I have that data and it should also update if I add more data so now we should another line below this that should be 8000 there we go that line is in here we should also be able to add in a middle so for example if I go to the first partial the last line is this one Julio and Adidas and 832 let's go find that and mark that line so that's this line 16 the next one is perineal which is coming from a different spreadsheet the second one which should be the first line over here now let's go to our first partial and add some more data to this so let's go check this out so if I go to my combined data see right below Julio now we have Thomas and then after that peroneal which is coming from the second spreadsheet so it's automatically updating as we keep adding new data and that should also by the way happen here because this is also using that same logic here see we have Thomas that 3333 and then if I scroll all the way down we have this so that's good so it's updating all of this now if you wanted to sort this if you're using this method then what you could do you could just go here you can add another class to this let me actually zoom in so you can see what I'm doing here here we go I can go back to this and do order by and then choose which column I want this sorted by so let's say I want to sort this by date right so that's gonna be call one so if I do this and hit enter this should sort all that data see by dates and if you wanted to have the latest one on top you could do the c4 descending and now it should have the latest on top and the earliest all the way down and if you wanted to sort by another column just change the column right so maybe you want to sort this by the sales rep so that would be column two and let's say I don't want this descending so you can either do ascending or not to anything now if you want to learn more about this I have a whole series of videos about query function but I'm gonna hit enter now I'm sorting by sales rep so that's if you want to sort this now if you want to sort this there is no select statement so you're not gonna be able to do that but what you can do you can just put this whole thing inside of a sort function like this and I need to close that function all the way down here so that will be the range I'm trying to sort so if I keep this like this it should just sort by the first column so if I'd enter C sort it again again from earliest date to latest date if you're not happy with that you can go here and add a comma and then choose which column you want to sort by so let's say you want to sort by sales rep right that would be column number two so if I do that it should oh so we have to do in pairs okay so we need to also say how we want to sort this so we're gonna do comma and do E's ascending so we'll say true hit enter and there it is now we're sorting it in ascending order by what did I do Oh second column which is salespeople now if you wanted to do descending you would just do false which would be names again from W through a if you wanted to sort by date descending then the column is one so the first column oops there it is and false is descending so the latest date on top or do true and that should be ascending hopefully that makes sense so if you need to sort it you can sort it so a couple of different ways to do this one is using filter function the other one is using query function and that should do it for this video thanks for watching please subscribe and I'll see you the next one

Channel: Learn Google Spreadsheets

Views: 69,358

Keywords: Combine, Google Sheets, Multiple, Workbooks, master, dynamic, file

Id: qsqjUxBcgAs

Length: 20min 58sec (1258 seconds)

Published: Mon May 11 2020