Doug: How to combine four sheets where each has a different number of rows?
Use Power Query
Format each worksheet as a Table with Ctrl+T
Rename the tables
For each table, new query From Table. Add a custom column for Region
Instead of Close & Load, choose Close & Load to… Only Create a Connection
Use New Query, Combine Query, Append. 3 or More Tables. Choose the Tables and Add
Close and Load and the data appears on a new worksheet
For the one table with extra column: the data shows up for only that sheet's records
For the one table where the columns were in the wrong order: Power Query worked correctly!
Easy to Refresh later
Use Power Query
Format each worksheet as a Table with Ctrl+T
Rename the tables
For each table, new query From Table. Add a custom column for Region
Instead of Close & Load, choose Close & Load to… Only Create a Connection
Use New Query, Combine Query, Append. 3 or More Tables. Choose the Tables and Add
Close and Load and the data appears on a new worksheet
For the one table with extra column: the data shows up for only that sheet's records
For the one table where the columns were in the wrong order: Power Query worked correctly!
Easy to Refresh later
Transcript of the video:
Learn Excel for MrExcel Podcast, Episode 2178. Merge Four Worksheets.
Today's question, via YouTube, from Doug.
He has this situation where he has four sheets, where each sheet is a region with sales data and the number of records change monthly.
And, right now, Doug is trying to use formulas, but when the number of rows change on him, it becomes a nightmare.
Right?
So, I said, Hey, Doug, can we use Power Query, if you have Excel 2010 or Excel 2013?
It's a free download from Microsoft, or it's built into 2016 and Office 365.
He says, Yes.
Alright, so, here's what we have.
We have four reports-- the Central Region, the East Region, the South Region, and the West Region-- and each one has a different number of records like, here, in the South Region, we have 72 records; in the East region, 193 records.
And this is going to change, right?
Every time we run this report, we'll have a different number of records.
Now, I've made some assumptions here.
First, that there's not a column called Central, and then, also, I'm going to be completely evil here and take the South Region, I want to try and screw it up.
I'm going to take the profit column, cut it, and paste it to reverse those, and then-- alright.
So, we have one where the columns are reversed and then another one where we're gonna add an extra column-- Gross Profit Percent, so this is gonna be profit divided by revenue.
In an ideal world, these are all shaped exactly the same.
But, as I learned recently-- I was doing a seminar down in North Carolina-- if they're not-- alright, someone had a situation, where, you know, halfway through the year things changed, and they added a new column, or move columns around.
We were really happy to see that Power Query was able to deal with this.
Alright, so we're going to take each of these reports and make it into an official table.
Format as Table.
So that's Ctrl+T, or you could use a name range-- for me, Ctrl+T is the easier way to go.
And what they do here, is they call this Table1.
I'm going to rename this to be called Central, and then we go on to East, Ctrl+T, click OK, and this is going to be called East.
Now, hey, on an earlier podcast, I showed how if these have been four separate files we could have used Power Query just to combine files, but that doesn't work when they're four separate or four worksheets in the same book.
So, well, there we go.
And, then, West, this, Ctrl+T-- a little tedious to set this up the first time, but, boy, there's going to be awesome every time you have to update this later on.
So, what we're going to do is, we're going to choose this first table, Central Region, and if you're in 2010 or 2013 and downloaded Power Query, you're going to-- it's going to-- have its own tab.
But in 16-- in Excel 2016-- it's actually Get & Transform, which is the second group, and in Office 365 its now Get & Transform, which is the first group.
And, so, we're going to say thet we're going to create this data From Table/Range.
Alright, and there is our data.
Now, we don't have a Region field and the combined files would have added the Region field.
So, in this case, I'm just going to Add Column, a new Custom Column, the heading's going to be Region, and this one is going to be-- what was this-- "Central", right?
Like that, click OK.
Alright, now, here's the important part: When we're done with this, we're going to go Home-- not choose Close & Load-- we're going to open the drop-down, Close & Load To...
Only Create Connection, click OK.
Perfect.
We have our Connection only.
Now, the next thing we have to do, is repeat these steps for the next three regions, and now that would be really a bit boring to you.
So, let's just speed up the video to 10x for this.
Alright, there we are.
Four connections set up now.
Here's where we're going to do the magic.
I'm going to insert a new blank worksheet, and I'm going to say, Get Data, Combine Queries, and I want to Append two queries from this workbook, and I'm going to say Three or more tables, and the available tables are Central through West, click Add.
BAM! Click OK.
And then we can Close & Load.
And what we have here is, we have a superset of all of the records in all the tables.
Alright.
And where we tried to screw it up-- where I purposely tried to screw up by reversing Cost of Goods Sold and Profit-- down in-- what was that, that was Central?
South?-- in the South Region.
I'll just go check those...
Alright, and it looks like-- yeah-- generally feels right.
They used the heading to figure it out because the Profit is always higher than Cost of Goods Sold, and so that worked.
And then down here in the West, where we added Gross Profit Percent, we actually get that data for the tables that had it; and for the tables that didn't have it, we just get null-- which is perfect.
Alright, now, Doug, here's what you're going to do: So, the next time that you have some more data-- and I'll just, let's create some some extra records here.
We'll just add some ABC, with a date of today, and all retail, and it's called Doug's New Records.
And just some garbage out here, let's just put in 100 all the way across, in the interest of time.
Okay.
So, now, because this is a table, the table automatically expands to the New Records, which is beautiful.
Had they been name range, I would have had to redefine.
That's why I really like the table instead of the name range.
But, we come back here to the resulting workbook with 563 rows loaded, and I click Refresh, and BAM! Now I have 572 rows loaded, including-- let's see if we can find them in here-- Doug's new records right there at the end of the South Region.
Isn't that just an awesome, awesome way to go?
Yes, it definitely takes longer to set up the first day.
We're up to seven minutes already if I hadn't sped that up to 10x.
But once it's set up, now, life is going to be super, super easy from here on out.
Well, hey, this is where I usually promote my own book, but, no, this time let's talk about this awesome book, "M is for (DATA) MONKEY," by Ken Puls and Miguel Escobar.
Everything I learned about Power Query, I learned from this book.
Click on the "I" on the top right-hand corner for more information about that book.
Alright, wrap up.
Topics in this episode, Doug: How to combine four sheets where each sheet has a different number of rows.
We can use Power Query, make sure to format each worksheet as a Table with Ctrl+T, or use named ranges, but I prefer Ctrl+T.
Rename the Tables, from each Table choose New Query from Table, add a Custom Column for a Region, and then instead of Close & Load, choose Close & Load To..., Only Create a Connection.
Do that for all four queries and then New Query, Combine Query, Append, Choose three or more tables, choose the tables and click Add.
Now, some older versions of Power Query, you couldn't do three or more tables-- you have to do two, and then do another query to add the third one, and then do another query to add the fourth one.
Either way, it would be more hassle that way.
I'm glad that they added the three or more tables.
Close & Load this time.
Close & Load to the worksheet and then later on, if you add more data to any of the four tables, just go back to your query and click Refresh and you're good to go.
Power Query, an amazing new feature from Microsoft.
I love it.
I want to thank Doug for sending that question in.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Today's question, via YouTube, from Doug.
He has this situation where he has four sheets, where each sheet is a region with sales data and the number of records change monthly.
And, right now, Doug is trying to use formulas, but when the number of rows change on him, it becomes a nightmare.
Right?
So, I said, Hey, Doug, can we use Power Query, if you have Excel 2010 or Excel 2013?
It's a free download from Microsoft, or it's built into 2016 and Office 365.
He says, Yes.
Alright, so, here's what we have.
We have four reports-- the Central Region, the East Region, the South Region, and the West Region-- and each one has a different number of records like, here, in the South Region, we have 72 records; in the East region, 193 records.
And this is going to change, right?
Every time we run this report, we'll have a different number of records.
Now, I've made some assumptions here.
First, that there's not a column called Central, and then, also, I'm going to be completely evil here and take the South Region, I want to try and screw it up.
I'm going to take the profit column, cut it, and paste it to reverse those, and then-- alright.
So, we have one where the columns are reversed and then another one where we're gonna add an extra column-- Gross Profit Percent, so this is gonna be profit divided by revenue.
In an ideal world, these are all shaped exactly the same.
But, as I learned recently-- I was doing a seminar down in North Carolina-- if they're not-- alright, someone had a situation, where, you know, halfway through the year things changed, and they added a new column, or move columns around.
We were really happy to see that Power Query was able to deal with this.
Alright, so we're going to take each of these reports and make it into an official table.
Format as Table.
So that's Ctrl+T, or you could use a name range-- for me, Ctrl+T is the easier way to go.
And what they do here, is they call this Table1.
I'm going to rename this to be called Central, and then we go on to East, Ctrl+T, click OK, and this is going to be called East.
Now, hey, on an earlier podcast, I showed how if these have been four separate files we could have used Power Query just to combine files, but that doesn't work when they're four separate or four worksheets in the same book.
So, well, there we go.
And, then, West, this, Ctrl+T-- a little tedious to set this up the first time, but, boy, there's going to be awesome every time you have to update this later on.
So, what we're going to do is, we're going to choose this first table, Central Region, and if you're in 2010 or 2013 and downloaded Power Query, you're going to-- it's going to-- have its own tab.
But in 16-- in Excel 2016-- it's actually Get & Transform, which is the second group, and in Office 365 its now Get & Transform, which is the first group.
And, so, we're going to say thet we're going to create this data From Table/Range.
Alright, and there is our data.
Now, we don't have a Region field and the combined files would have added the Region field.
So, in this case, I'm just going to Add Column, a new Custom Column, the heading's going to be Region, and this one is going to be-- what was this-- "Central", right?
Like that, click OK.
Alright, now, here's the important part: When we're done with this, we're going to go Home-- not choose Close & Load-- we're going to open the drop-down, Close & Load To...
Only Create Connection, click OK.
Perfect.
We have our Connection only.
Now, the next thing we have to do, is repeat these steps for the next three regions, and now that would be really a bit boring to you.
So, let's just speed up the video to 10x for this.
Alright, there we are.
Four connections set up now.
Here's where we're going to do the magic.
I'm going to insert a new blank worksheet, and I'm going to say, Get Data, Combine Queries, and I want to Append two queries from this workbook, and I'm going to say Three or more tables, and the available tables are Central through West, click Add.
BAM! Click OK.
And then we can Close & Load.
And what we have here is, we have a superset of all of the records in all the tables.
Alright.
And where we tried to screw it up-- where I purposely tried to screw up by reversing Cost of Goods Sold and Profit-- down in-- what was that, that was Central?
South?-- in the South Region.
I'll just go check those...
Alright, and it looks like-- yeah-- generally feels right.
They used the heading to figure it out because the Profit is always higher than Cost of Goods Sold, and so that worked.
And then down here in the West, where we added Gross Profit Percent, we actually get that data for the tables that had it; and for the tables that didn't have it, we just get null-- which is perfect.
Alright, now, Doug, here's what you're going to do: So, the next time that you have some more data-- and I'll just, let's create some some extra records here.
We'll just add some ABC, with a date of today, and all retail, and it's called Doug's New Records.
And just some garbage out here, let's just put in 100 all the way across, in the interest of time.
Okay.
So, now, because this is a table, the table automatically expands to the New Records, which is beautiful.
Had they been name range, I would have had to redefine.
That's why I really like the table instead of the name range.
But, we come back here to the resulting workbook with 563 rows loaded, and I click Refresh, and BAM! Now I have 572 rows loaded, including-- let's see if we can find them in here-- Doug's new records right there at the end of the South Region.
Isn't that just an awesome, awesome way to go?
Yes, it definitely takes longer to set up the first day.
We're up to seven minutes already if I hadn't sped that up to 10x.
But once it's set up, now, life is going to be super, super easy from here on out.
Well, hey, this is where I usually promote my own book, but, no, this time let's talk about this awesome book, "M is for (DATA) MONKEY," by Ken Puls and Miguel Escobar.
Everything I learned about Power Query, I learned from this book.
Click on the "I" on the top right-hand corner for more information about that book.
Alright, wrap up.
Topics in this episode, Doug: How to combine four sheets where each sheet has a different number of rows.
We can use Power Query, make sure to format each worksheet as a Table with Ctrl+T, or use named ranges, but I prefer Ctrl+T.
Rename the Tables, from each Table choose New Query from Table, add a Custom Column for a Region, and then instead of Close & Load, choose Close & Load To..., Only Create a Connection.
Do that for all four queries and then New Query, Combine Query, Append, Choose three or more tables, choose the tables and click Add.
Now, some older versions of Power Query, you couldn't do three or more tables-- you have to do two, and then do another query to add the third one, and then do another query to add the fourth one.
Either way, it would be more hassle that way.
I'm glad that they added the three or more tables.
Close & Load this time.
Close & Load to the worksheet and then later on, if you add more data to any of the four tables, just go back to your query and click Refresh and you're good to go.
Power Query, an amazing new feature from Microsoft.
I love it.
I want to thank Doug for sending that question in.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
