Google Sheets - Combine Data From Multiple Sheets (Tabs) Tutorial

in this video I'm going to show you how to combine multiple tabs of data or multiple sheets however you want to call this things to one master sheet so if you look I have some partial data on this first tab I have some more data on my second tab I have another worksheet here on the third tab and what I want to do I want to simply have all of this data on my master tab so the first thing I'm going to do just go ahead and copy all of this on top just right just like that so we have some labels and then we'll go ahead and start combining this data so I'm going to use array Matic to do this and this is the way it's going to work I'm going to start in my equal sign I'm going to open the curly bracket and that's our sign for an array and I'll go ahead and go to my first partial data tab highlight all of my data now pay attention I'm not including the labels on top in this particular case right so that's that I'm gonna go ahead and do a comma here so actually I do it semicolon sorry about that so now I'm gonna go to my partial to data I'm going to highlight exactly the same size of data right here and again it doesn't matter how large it is but again I don't want to include the labels and the second thing you want to make sure that the columns are in the same order on all of these tabs right so I have the third tab as well so I'm gonna do another semicolon go partial data tree go ahead and highlight this data and I don't have any more so I'll go ahead and close my curly brace here hit enter and you should see how all the data is now from all different tabs combined so we go to partial data one we have like 11 records here we have 11 records on partial data two and we have oh it eleven of them look at that so eleven eleven eleven so we should have 33 so there it is 34 and one is the label right so that's 33 so there we are so we have our combined data all of it together but the challenge here is that I want to be able to make something but you can go and just add another record so I'm just gonna copy this record and paste it here and have it add to our master list without having to go back and redo the formula but right now it's not gonna do that because we just mentioned a particular range and because that new row is not included is not gonna work now usually the way you basically start from a particular cell and keep scrolling down is by using not using the end in your range so I'm saying from a two through G and then from A through to G a truth a two through G this way I'm saying just start from the a two and then old all the way down in our G column now I did this and if you pay attention it looks like it didn't work right so when we look at this so I have this 13 records which actually come from this first one well actually twelve records but anyway it doesn't matter and it seems like there's no more records but what happened here is not that there are no more records is that if I keep scrolling down at some point we are going to see the second data piss and then keeps going on we're gonna see the third data piece so what's happening here is that not that it doesn't work it works but it basically takes this entire thing with all the blanks all the way down and includes all of these empty rows in it and then the second one a third one and basically makes a big tab out of all of those so that's what we're having right now so what we need to do basically is come up with something that's just going to remove all of those empty rows in between our data so in order for me to do this this is what I'm going to do I'm going to grab this array formula that I have that returns this I'm going to cut that and I just want to remember so this is what it is right that's what it returns so we want to keep that in mind so I'm gonna cut that because I'm gonna need that formula and what I'm going to use I'm going to use query formula so the first thing in this function is going to be basically the data so the data is going to be that array that we had so I'm simply going to paste my array that was returning our data and I'm going to do comma and the next thing in this function is the query so the query needs to be as a string or as text means in quotes and I'm going to use my query which is gonna be select and I'm going to use Asterix for all columns and I will have to filter the data so the filter the data will have to use my where statement and I'm going to say where so I'm gonna say where the column 1 so in our data the column 1 is not no so I want it when it's not no when it's not empty so comma again I have a lot of videos about query function on a channel if you want to learn a lot more about this function please go ahead and watch those videos but right now I'm going to quickly explain what this says basically I chose my data right here and from that data I'm saying select start and start means let's select all the columns from the data and then I filter that data I'm saying let's filter data that data using column 1 and column 1 in our data is our dates so I'm saying let's pull only the dose Rose where the date is not empty so that's essentially what this says and the headers is basically the number of headers in our data the headers here as you can see here on top Rand called sales cost of goods etc now if you remember when I was selecting the actual data in my array I didn't actually select those headers so I don't have any headers so zero goes here because our parentheses here I'm gonna hit enter and I'm gonna get my results right here right so the main thing you want to pay attention to here is that currently the only reason is that this works is that we always have dates so you want to do this on a column in your data where you always have values so if you know that like your C column is some sort of idea or something that's always gonna have some value and use that color instead of column one use column 2 column 3 which our column works now if you don't have such column then is gonna be more complicated your query is gonna be much longer so what you would have to do in those cases you would have to chain a lot of ORS you would have to say C I'm saying where column one is not know and then you will say or column two is not no or column three is not no or column four is not no hopefully you won't have to do this right or column four is not no you get the idea you have to do this for all the columns right so I did it for one two three four so far I have three more to go and I'm gonna do or column five is not no or column six is not you know now thinking of looking at this I should have copied and pasted that or column 7 is not know hopefully I didn't miss type anything as I was doing this whole thing or forget something on the way so there it is I'm gonna hit enter I'm getting my data so I'm fine so that would be the full way of doing this if you don't have a single column you can trust right so there it is I get my data so as you can see I have 38 rows and I want to go back here and add some more rows here so I'm gonna copy a couple of more those 38 so this should make it 40 right so there it is 40 and it doesn't really matter to which one I'm adding so if I go ahead to my second tab at number 3 more see it's here in my master cap now the last thing we want to test is that this works on empty rows not on empty cells right so if we have an empty cell here like midwestern is missing I still want to make sure the data falls true there this so we have the data now the same could appear here so if I have some things missing like those I still want this row to appear so if I go here that looks good but if none of those are there delete this one too this should not be there now so let's go and check this out and see how this works out for us so we shouldn't have this one we should have this one so going back here perfect it's gone and we just had this one and this one right above it so this works just fine and that's how we can combine our data hopefully that makes sense please join the channel subscribe and I'll see you in my next video

Channel: Learn Google Spreadsheets

Views: 402,054

Keywords: Google Sheets, Combine, Data, From, Multiple, Sheets, Tabs, Tutorial, master, spreadsheet, automatically, fomula

Id: q8awNSYNdq4

Length: 11min 3sec (663 seconds)

Published: Fri Sep 22 2017