To buy or try WBMerge: https://mrx.cl/2HVj0wU
Save 20% on WBMerge through June 30 2019 using discount code MREXCEL
In this video, I try the WBMerge utility on a collection of Excel workbooks that might be problematic in Power Query.
WBMerge is a $39 utility from Workbook Tools. The utility can merge data from multiple Excel workbook or multiple Google Sheets files.
Save 20% on WBMerge through June 30 2019 using discount code MREXCEL
In this video, I try the WBMerge utility on a collection of Excel workbooks that might be problematic in Power Query.
WBMerge is a $39 utility from Workbook Tools. The utility can merge data from multiple Excel workbook or multiple Google Sheets files.
Transcript of the video:
Learn Excel for MrExcel Podcast Episode 2280. WBMerge Using Different Columns.
Welcome back to the MrExcel netcast today.
Today, we take a look at some brand new software that I've been trying out called WBMerge.
It stands for workbook merge.
Its for when you have dozens or hundreds of files and you need those files to all be combined into one workbook.
You'll say isn't this like Power Query?
But it has some cool features that Power Query doesn't have. Let's check it out.
Alright, WBMerge is a brand new utility that let's you merge data in different workbooks.
But WBMerge can do some things that Power Query can't do.
So what I have today I have four different files.
Q1 through Q4 and you see that my headings are not in row 1.
They are in row 5 and that always causes problems with Power Query.
So we have Apple through Iceberg and we move on here to Q2.
You'll see that Iceberg is gone and we've added a Kiwi and Lime. Then now here in Q3.
This is where I really threw a curveball.
Because I have the same columns as in the previous ones, but I've completely mixed them up.
I sorted this data left to right, all right we'll see how it works.
If it works if WBMerge gets it right, we should see these numbers in sequence 332-333-334 and so on.
That's what we're hoping and then here we have some of the columns in common Honeydew Iceberg, Jackfruit, Kiwi. But then we have some.
other columns that are not in any of the worksheets.
Now in order to make this work, we're going to do is we come out here and create one template file or or just a sample file and the headings have to be in row five like they are in all of the other items and a super set of all of the columns that we might ever get.
We want these to be in the order that we want the final file to be.
Alright, I'm going to close all this and fire up WBMerge.
OK, so here's The WB merge application and the first thing to do is specify a source file.
In other words, the file that has all of the Headings and it says hey are all of data headers on row one of each worksheet, and I must say no.
Point to my all possible columns worksheet and see it works with XLSM, which is beautiful.
And sheet1. The headers are in row 5.
The Selection is complete.
If you had multiple sheets, I guess you could use this to set them. So press Selections Complete.
Are you done? Yes, Workbook successfully loaded. Click OK.
Alright so now it knows which columns I want and I want all of the columns there.
Alright then we go on to uncheck Strict Column merge as I want allow partial column merges.
Yes Strict Worksheet Merge.
Now, in this case all the workbooks have just one worksheet so.
That's really not necessary to uncheck that. And result shading.
I'm going to leave that on.
I think that's a cool feature, so we choose merge and we want to add items by files or folders.
I'm going to do it by files.
So I browse for the folder.
WBMerge.
How the new file is going to be called AllD and they're going to add XLSX to the end. Alright, that's great so we will add items here.
I want Q1 through Q4.
I'm not going to include this one because that was just a dummy file that specified all the columns. So we choose open.
All right and then finally Run Merge and this is lightning fast.
Merge complete.
Completed successfully all workbooks and there's a whole bunch of information here as far as what happened and I don't pay attention to that at all, right?
Let's go check it out in Excel.
All right, so here we are.
This file is just the same results file.
In case you have to go back and check anything, here is the real file.
So Apple, Banana, Cherry everything looks great for that file.
One I love that they had this file number out here and they also alternate the colors which is cool.
WB-Null just means there was nothing in these columns.
I guess I'd probably do a Ctrl+H and change all that to just blank cells, which is what I would prefer. Whatever.
So then here in File2 you see that iceberg was removed and we added Kiwi and lime, right?
Here's the real test: File3 where things were completely out of sequence.
But thanks to the heading row in row five, it was able to bring all that back.
Nice job there WBMmerge! And then here where I had a lot of things towards the end of the list and nothing towards the beginning of list. All right.
So you know, hey this is cool. It has some flexibility.
It was designed for hundreds of files, but this little test here.
Yeah, it's worth checking out.
Check the link down in the YouTube description.
Well, there you have it. That's WBMerge. What do you think?
Leave me a note down in the comments below and if you like this video please subscribe and ring that Bell. Hey, thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
Welcome back to the MrExcel netcast today.
Today, we take a look at some brand new software that I've been trying out called WBMerge.
It stands for workbook merge.
Its for when you have dozens or hundreds of files and you need those files to all be combined into one workbook.
You'll say isn't this like Power Query?
But it has some cool features that Power Query doesn't have. Let's check it out.
Alright, WBMerge is a brand new utility that let's you merge data in different workbooks.
But WBMerge can do some things that Power Query can't do.
So what I have today I have four different files.
Q1 through Q4 and you see that my headings are not in row 1.
They are in row 5 and that always causes problems with Power Query.
So we have Apple through Iceberg and we move on here to Q2.
You'll see that Iceberg is gone and we've added a Kiwi and Lime. Then now here in Q3.
This is where I really threw a curveball.
Because I have the same columns as in the previous ones, but I've completely mixed them up.
I sorted this data left to right, all right we'll see how it works.
If it works if WBMerge gets it right, we should see these numbers in sequence 332-333-334 and so on.
That's what we're hoping and then here we have some of the columns in common Honeydew Iceberg, Jackfruit, Kiwi. But then we have some.
other columns that are not in any of the worksheets.
Now in order to make this work, we're going to do is we come out here and create one template file or or just a sample file and the headings have to be in row five like they are in all of the other items and a super set of all of the columns that we might ever get.
We want these to be in the order that we want the final file to be.
Alright, I'm going to close all this and fire up WBMerge.
OK, so here's The WB merge application and the first thing to do is specify a source file.
In other words, the file that has all of the Headings and it says hey are all of data headers on row one of each worksheet, and I must say no.
Point to my all possible columns worksheet and see it works with XLSM, which is beautiful.
And sheet1. The headers are in row 5.
The Selection is complete.
If you had multiple sheets, I guess you could use this to set them. So press Selections Complete.
Are you done? Yes, Workbook successfully loaded. Click OK.
Alright so now it knows which columns I want and I want all of the columns there.
Alright then we go on to uncheck Strict Column merge as I want allow partial column merges.
Yes Strict Worksheet Merge.
Now, in this case all the workbooks have just one worksheet so.
That's really not necessary to uncheck that. And result shading.
I'm going to leave that on.
I think that's a cool feature, so we choose merge and we want to add items by files or folders.
I'm going to do it by files.
So I browse for the folder.
WBMerge.
How the new file is going to be called AllD and they're going to add XLSX to the end. Alright, that's great so we will add items here.
I want Q1 through Q4.
I'm not going to include this one because that was just a dummy file that specified all the columns. So we choose open.
All right and then finally Run Merge and this is lightning fast.
Merge complete.
Completed successfully all workbooks and there's a whole bunch of information here as far as what happened and I don't pay attention to that at all, right?
Let's go check it out in Excel.
All right, so here we are.
This file is just the same results file.
In case you have to go back and check anything, here is the real file.
So Apple, Banana, Cherry everything looks great for that file.
One I love that they had this file number out here and they also alternate the colors which is cool.
WB-Null just means there was nothing in these columns.
I guess I'd probably do a Ctrl+H and change all that to just blank cells, which is what I would prefer. Whatever.
So then here in File2 you see that iceberg was removed and we added Kiwi and lime, right?
Here's the real test: File3 where things were completely out of sequence.
But thanks to the heading row in row five, it was able to bring all that back.
Nice job there WBMmerge! And then here where I had a lot of things towards the end of the list and nothing towards the beginning of list. All right.
So you know, hey this is cool. It has some flexibility.
It was designed for hundreds of files, but this little test here.
Yeah, it's worth checking out.
Check the link down in the YouTube description.
Well, there you have it. That's WBMerge. What do you think?
Leave me a note down in the comments below and if you like this video please subscribe and ring that Bell. Hey, thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
