Combine Data/Sheets in Excel & Google Sheets

so in this video we're going to go over all different ways we can combine ranges in both Excel and Google Sheets so we'll start with a couple of core functions that will help us combine our data and then after that we'll talk about a few other functions that will help us do some interesting things so let's just start with our Basics so first of all if I have two ranges one here one here I should be able to just combine them using something like the stack so this stack is a function that allows you to pass comma separated list of ranges and get a result so for example if I select this comma and then this and then close parentheses hit enter you'll see what it returns is those two ranges combined together and you can keep doing this for as many as you like so if you add a comma here it will keep adding more right so I can go ahead and select another range after this and so on and so on after commas now for this example I'm just gonna go with two ranges like this so This stack is your vertical stack so we're stacking two ranges on top of each other now we also have H stack so each stack is going to stack your ranges horizontally so age for horizontal V for vertical so basically the way it would look like if I for example have this comma and then this what that should give me is that region followed by sales of course I can then also do another H stack for this followed by this and now that's another range and then if I wanted to take this and this and put it on top of each other then we could just put both of these inside of a v-stack so if I cut this out and go here and I'm gonna do v stack and then as a first range we'll have this H stack and then I'll do a comma and I'll paste the other H stack here and of course we have to close parentheses for that v stack and now if I enter you'll see what we get is basically combined data from these two but this way we only choose certain columns instead of all columns from that data table and it still works and of course if you wanted to do three columns so let's say I want to add the states last column here I can do a comma and add those two and if I'd enter you'll see now we get that combined data and it gets us basically our results in the order we want them so in Google Sheets we can also combine our ranges using arrays right so the regular array method where we take this first bracket and we'll select this and then we do for example semicolon and select this and then if I close this and hit enter you'll see it basically gives me that combined range like this now this wouldn't work in Excel though this is just Google Sheets thing that works like this but the difference between this and what I just did is if you use v-stack and H stack so let me actually do a v stack again so with this stack you can actually select one range comma and then select another range that has a different size so notice that in this the first one I select has four columns and the second one has three columns and if I close parenthesis and hit enter I will not get an error what it will do it will basically just replace these with n a's and then if you want to fill those with some default value you can still do that by using something like if error so I can do if error open parentheses and then go after this whole thing and do a comma and let's say when there are no sales amounts I want to just do a zero for those so if I do this hit enter you'll see it will basically take all of these replace them with a zero now you'll see that it gives me these that's basically just formatting so if you get these instead of your dates that just means just go ahead and switch that to your date format and you should be fine right so that's pretty much all you need to worry about here so that's our v-stack H stack and as you can see we can also use if error to automatically fill any values that are Nas with something else using something like if error and of course you should be able to also use something like FNA if you don't want to use if error you should be able to use this too okay so now that we know all this bass Stuff how to use v-stack H stack let's actually try to create something more Dynamic so what I mean by that I'm going to get rid of all of this and if I wanted to do v-stack and I wanted to just combine this and this other table I might want to do this in a way that if I add more lines to this those are automatically included in my results so therefore I have to select this extra lines and then if I do a comma I do this I'm just trying to do this formula right next to each other so it's easier to see what's going on typically you wouldn't put this v stack right below these but if I just go ahead and do this and press enter you'll see what it's going to do because we have blanks it will basically have those blanks sitting here because I did select those extra lines right so those are now going to be included and then of course there are also some empty lines here that we cannot see but those are a part of this from here so the question is how we can make this better so we don't have to deal with this empty stuff in a metal and for this what we can do we can basically try to check if the whole line is empty then let's not include the line now the way I'm going to do this is by using a few functions functions like filter and I'm going to use some Lambda and by row a lot of these are pretty complicated functions so I'm not going to go too much in depth about how each one works but I do have videos covering each one separately but essentially what I want to do I want to do something like this I want to be able to join this whole line together so if I wanted to do that one way to do this is to for example use something like text join and as a delimiter I would do nothing double quotes comma ignore empty I'm going to say false comma and then finally as text if I just select this whole line like this what that's going to do it's gonna basically combine this whole line of data to one cell and then I could of course drag it down like this to get that for every line and the logic behind this is that now in a separate column we could check if this is not equal blank or if it equals blank I'm gonna do naughty cools blanks so if I drag this now what would happen is that these lines get a true and these get a false and basically false would be the lines we don't want to be included now finally we can now take this and get our data using filter functions so with Filter function we can select this comma and then as a condition as a matter of fact instead of selecting the whole thing let's go ahead and select this much without this part and then comma and then finally I'll do this which is the range of trues and falses so if I do this you'll see that what it's going to do it's just going to keep the ones that are true and it's going to exclude all these blank lines now this should probably not go this far it should go until here but other than that that's going to be our logic of getting here now of course you can do one formula here and then do another formula here and then do another formula here and then do this formula to get your results but the difficulty for us is going to be to combine all the steps in a single formula so how are we going to do that exactly so the hardest part here is going to be this one to come up with a way to have this text join function together with this true and false statement in one formula how do we get something like this to work so let's start with this text join so we basically need this text join function in one formula without having to copy this down to get these results and to do that we can use by row function and by row is a Lambda array function again I have videos covering these in further detail I highly recommend watching those to really understand what's going on here because these are pretty complicated but essentially to replicate what I just had what I'm going to do I'm gonna go ahead and select this data like this comma and then I'm gonna pass what's called a Lambda function and this Lambda function is going to accept each line from this range as an argument so we'll just call that R short for row comma and then after R we're going to do formula expression that's going to be what formula we want to apply to each line from this table and the formula we're going to do is that same text join formula and that text join if you remember has empty delimiter comma ignore we set false if you do true it doesn't really matter on this one and then finally the text instead of selecting this line I'm just going to use that r which is that argument close parentheses and then I'm going to close parenthesis again for that Lambda function and then close parentheses again for that by row function now this R is a variable you can call it anything you want you just have to match to be the same in both cases now at this point if I press enter notice that that formula with one formula I got this basically that combined result in this one cell now at this point I want to check if that equals blank or not that's the next step I'm gonna do and we'll do that in a second for now let's just remember that this formula will produce this so now let's put this inside of that filter function so I'm going to take this cut that and we'll do a filter function and for filter function I'm going to use this range right here comma and then we need What's called the condition condition would be an array of trues and falses and the way we're going to get that array of trues and falses which is going to paste that by row function which basically gives us this column and we're going to check if that's not equal blank which is double quotes close parentheses hit enter and you'll see that this month formula it kind of goes to the next line let me zoom out maybe a little bit here so that this sits on a single line you can see this one formula will now take this range add this extra column and check whether that's empty or not and give us only whatever's not empty which means at this point we can get rid of all of this and this should still work by itself now at this point we just have to combine this with this now before I do the combination I want you to pay attention to this if I go back to this formula you'll see that within this formula I use this same range twice so I did that range once here for the filter function and then I use that same range all over again for this by row function so I want to avoid using the same range twice in a formula so what I'm going to do here I'm going to use let function to do this so what I can do I can do let and then with let function actually let me do this separately let me hit Escape I'm going to go back let me just cut this and I'll paste this later so I'm going to cut this and I'm going to use let function here so with let function we start by providing a name for a variable so I'm just going to call that variable data comma and then we need some sort of expression that will basically be stored within that variable data and for me that expression is just going to be this range which was that repeating part if you remember in my formula so I'm gonna do a comma and at this point now after this comma we can refer to this range as data because we just stored it in this variable so I'm just going to go ahead and paste the formula I had right after this comma so I'm going to paste this and at this point I'm just going to close parentheses because we need one to close this let function and what I can do I can just replace all these occurrences with that variable name data and by doing this it will still work but we only have to refer to this range just once and the reason this could be helpful is because if I have to put something like this to get the range which is this v stack so I'm going to go here and combine this copy this hit Escape go back here now I just have to paste it in a single spot right here so if I just replace that range and paste that v stack which is the part that combines those two ranges if I enter I'll get all of these combined and at this point I don't really need any of this I just have one formula that gets me all of these so I'm going to cut this for a second hit enter and paste it over here so you'll see we got now one formula that combines these two ranges removes any blank lines from those ranges and then of course if you keep adding new data it will actually expand and add those new lines to this now first of all let me just copy paste the same exact formula in Excel just to confirm that this works in both so I'm going to copy this formula go to my Excel spreadsheet and this is the same spreadsheet as you can see I'll just basically just paste it hit enter and as you can see we got our results and again we have that problem with this type of formatting so short date that's what we want to do and you can see this works just fine again we're able to take those two tables and combine them and get combined range out of it and at this point we can now I'm going to go back to Google Sheets in this one so that's now our new formula so I could now reuse this formula for many situations so for example let's look at this example I have this data part one where I have this table with this columns and I have this data part 3 which has the same columns in the same order in this tab so if I wanted to combine those two things together and create like a combined sheet I can create another tab call it combined and what I'll do I'll just go back here grab this formula as is copy this and just go back to this combined and I'll paste it here and then of course this is not going to point to the right thing right now because this is pointing to the wrong range we need to update this range to point to the range in data one and this range to point to our range in data part three right and you can see it's just as simple as replacing the range I'll just keep this simple I'll just hit equal sign go to data part one I'll select this and as many lines as you want to go to that's completely up to you at this point I hit enter so the only reason I was doing this is to just get this syntax so at this point now we can use this as one of those ranges so I'm going to cut this get out of this here go back here and replace this first range with this range which is the part from the first tab and then of course I need another one now we get a reference error because we still have inaccurate range here but we're going to fix that very soon so I'm going to hit equals go to part three select this however far you want to go that's up to you and then of course we have to copy this get rid of that go back to our formula and finally replace this with that range of course you can just do that range right away I was just trying to kind of do more friendly way for basic users to understand what I'm doing here but basically at this point we can just keep comma separating as many of these as we like so if I just go here and add a comma and keep adding ranges from different tabs it will just combine those together and of course it will remove the blanks and just show us the data so at this point I can just copy the headers go to this combined and paste and there it is we got our combined results and then of course if I go to one of those and add a line or two I should be able to see that this line is automatically included here there it is as the last line from the table and of course if we add some data to that other table two it will automatically include that here too and basically now we have just this one formula where you can just comma separate as many ranges as you want and that should just give you your combined data and of course that same formula it's gonna work in Excel too so if I copy this go to my Excel spreadsheets and add a new tab called combined and simply just paste that here I'm going to take this column and make it date and there is that combined thing again it's the same spreadsheet same idea you can of course copy paste your headers and get your results right here finally I want to quickly go over cases when your columns don't exactly match now for example I have this data part 2 and in this one these columns are not in the same order and it's not the same number of columns so if I just highlight the range it wouldn't work so basically what you would have to do you would have to build again that range using your v-stack and H stack so if you look at this combined formula I just did right here this is just v-stack and we're v-stacking all these different ranges now assuming you have the same structure for each one of those tables then this is fine you can do this but if you don't then you probably need to select different columns from different tables to get this done so let's say I want to grab date and then state and then sales and then I have all of those three columns date state sales here and the same here date State and sales so if that's what I want what I'm going to do I'm gonna basically go here and I'm gonna do this separately and then I'm gonna put it back in that formula so I'm going to use H stack and for each stack I'm just gonna go to the first table and I'm going to select those columns in the order I want so I have date comma then I said state comma and then I said sales and of course if you have more columns you just select those columns just make sure you match your range sizes and if I close parentheses hit enter that should give me those three columns from my first table now I'm going to take that cut it and I'll go to my formula and I'll replace the first range I just did here with that H stack formula and we're going to of course replace the second and third one too so I'm going to hit enter and that gives us this not to make this simple I'm going to go back here let's just for now remove the second range here and we'll just keep that first H stack here by itself within that v stack so if I enter so that this is easy to understand we have our three columns in this order we got date we got state and we got sales and we can get rid of all of this so now I'm gonna do basically another H stack for another table so I'll just go here and do H stack and I'll do part two now I'll go here select this comma this comma this close parenthesis hit enter and that's our second H stack so now I can grab that formula without equal sign copy that get rid of this go back to this formula and right after this first H stack still within v stack I'm gonna do a comma and paste that second H stack and if I enter that should now combine those two and then we can of course just keep doing this so I'm going to do this one more time H stack I'm going to go to part three select this comma this comma this close parentheses hit enter there it is copy that formula get rid of this go back to my first Formula and after I did my second H stack which ends right here I'm gonna do a comma and paste another one and basically the idea here is you can just keep doing this as many times as you need and this would work just fine now this gets a little hard to read at this point so something we can do to make this a little easier is to just push some of these things to a new line so if I do alt or option enter to push this H stack and then alt and option enter and then alt an option enter not and but or I guess so basically Alt Enter on a Windows option enter on a Mac so there you go now this way it's a little easier to read you can see this is the first H stack second H stack third H stack and you can just keep doing commas and doing another H stack after this and that should give you your combined results once again let's just test the same exact formula now in Excel 2 so that you can see it works exactly the same way so I'm going to go to my Excel spreadsheet and here I'll get rid of this formula and I'll just paste this formula instead and that should give me as you can see my results we just have to put the right column names in the right spot and there it is we got our combined results and with this setup as we add more information to part one part two or part three it should automatically be included in our combined results so let's just do a quick example here let's say I want to add another data point for our last table here so last table here would be part three so here if I go and copy one of these lines paste here change the date change the numbers if I go back I should be able to see that this sales amount this date and this state now shows here as our last data point and that's how we combine multiple tables sheets Etc and this should give you the flexibility to combine your sheets ranges whatever you want to call them in multiple sheets and get one combined result in any way you like and that should do it for this video thanks for watching please subscribe and I'll see you next time