To buy or try WBMerge: https://mrx.cl/2HVj0wU
Save 25% on WBMerge through March 21 2020 using coupon code MREXCEL
You have an Excel workbook and would like to split it by (customer or product or whatever) with each unique value going to a new worksheet or to a new workbook. The people who made WBMerge have added a WBSplit feature.
Save 25% on WBMerge through March 21 2020 using coupon code MREXCEL
You have an Excel workbook and would like to split it by (customer or product or whatever) with each unique value going to a new worksheet or to a new workbook. The people who made WBMerge have added a WBSplit feature.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2312.
Split two worksheets. Hey welcome back back to the MrExcel Netcast.
I'm Bill Jelen. Let's follow up on something from 2019.
A review of a product called WB Merge and I'll put a link up there in the (i) to go check out that video.
It shows you how to merge several worksheets together and I was talking to John, the guy that wrote it.
I said hey, there used to be a great utility from Data Pig Technologies (before that website went dark).
It was called Workbook Split. You have a utility here that that.
merges things together.
Why don't you build a utility that will split things back apart?
And he did it right so I want to show you how cool. This is my data. Today, 563 rows.
I have 27 unique customers in column E and I want to split each customer to a new worksheet and what I actually have here is I copy that data out to workbook called Split Me and in order for this to work. You have to name your range.
The column names have to be in the first row of that named range so I selected all this data here.
Came up to the name box type SplitMe - no space there. By the way, press Enter and that creates a name right?
So we have that file.
Close that and then will come over here to WB merge and this is the interface I showed you in that last video, but extra tools Worksheet Split.
Right, so how to split a workbook and tells you here to define a name and you can be anything you want, right as long as it's just a single word. So select the workbook.
SplitMe.
Please select the named range to split.
Alright, so it's that one right there.
Select an select range to split alright and then which columns I opened this column. I want to split based on customer.
So the Customer column.
Said the split column if there was some customers I didn't want, like we had a an internal customer, I could uncheck them.
That's nice.
Now the Max that we have here is 100, which is probably about right.
'cause if we're creating worksheets in a workbook you're limited by available memory. I've seen 100.
I've seen I've seen maybe 150, but 100 is the limit and we'll live with that.
Next step: Which columns do we want? I want all of those columns.
Click OK and next step are we going to "Split to Worksheets - One worksheet within a book" or "Split to a new workbook"?
So I'm going to Split to Worksheets.
Enter the target folder, so let's just choose this PC.
C:\ and my AAA folder. Click OK.
And then the new workbook name will be called MySplitResults (typing) MySplitResults and XLSX will be added automatically And then right down here Execute Split.
Man is that fast. The split completed successfully.
I will come back to our AAA folder and there's MySplitResults.
And. There we are - look at that.
So for each customer, ABC Stores had four records.
AT&T had a whole bunch of records. You see that AT&T?
That ampersand, that's illegal character.
So "A T dash T", Bank United Boeing, Citigroup, Compaq, Cummins Engine ... all the way through.
Really, really cool bit of technology there now.
Now what if you want these to be individual workbooks, individual workbooks?
So let's come back to Workbook Split. This time I'll say Split to Workbooks.
Please enter the target folder.
C:\aaa\ great and Execute Split Again, amazingly fast.
And if we come back here to our folder you see that I now have 27 new workbooks.
Each workbook has the records from that customer.
Awesome awesome tool here WBMerge now offers WB split.
check down in the YouTube comments for a link to that product.
If you have to do this routinely. Boy, what a great product.
Check out my book MrExcel LX the Holy Grail of Excel Tips.
Click that "I" in the top right hand corner.
Hey if you like the tips in this video please please click Subscribe. And ring that bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Split two worksheets. Hey welcome back back to the MrExcel Netcast.
I'm Bill Jelen. Let's follow up on something from 2019.
A review of a product called WB Merge and I'll put a link up there in the (i) to go check out that video.
It shows you how to merge several worksheets together and I was talking to John, the guy that wrote it.
I said hey, there used to be a great utility from Data Pig Technologies (before that website went dark).
It was called Workbook Split. You have a utility here that that.
merges things together.
Why don't you build a utility that will split things back apart?
And he did it right so I want to show you how cool. This is my data. Today, 563 rows.
I have 27 unique customers in column E and I want to split each customer to a new worksheet and what I actually have here is I copy that data out to workbook called Split Me and in order for this to work. You have to name your range.
The column names have to be in the first row of that named range so I selected all this data here.
Came up to the name box type SplitMe - no space there. By the way, press Enter and that creates a name right?
So we have that file.
Close that and then will come over here to WB merge and this is the interface I showed you in that last video, but extra tools Worksheet Split.
Right, so how to split a workbook and tells you here to define a name and you can be anything you want, right as long as it's just a single word. So select the workbook.
SplitMe.
Please select the named range to split.
Alright, so it's that one right there.
Select an select range to split alright and then which columns I opened this column. I want to split based on customer.
So the Customer column.
Said the split column if there was some customers I didn't want, like we had a an internal customer, I could uncheck them.
That's nice.
Now the Max that we have here is 100, which is probably about right.
'cause if we're creating worksheets in a workbook you're limited by available memory. I've seen 100.
I've seen I've seen maybe 150, but 100 is the limit and we'll live with that.
Next step: Which columns do we want? I want all of those columns.
Click OK and next step are we going to "Split to Worksheets - One worksheet within a book" or "Split to a new workbook"?
So I'm going to Split to Worksheets.
Enter the target folder, so let's just choose this PC.
C:\ and my AAA folder. Click OK.
And then the new workbook name will be called MySplitResults (typing) MySplitResults and XLSX will be added automatically And then right down here Execute Split.
Man is that fast. The split completed successfully.
I will come back to our AAA folder and there's MySplitResults.
And. There we are - look at that.
So for each customer, ABC Stores had four records.
AT&T had a whole bunch of records. You see that AT&T?
That ampersand, that's illegal character.
So "A T dash T", Bank United Boeing, Citigroup, Compaq, Cummins Engine ... all the way through.
Really, really cool bit of technology there now.
Now what if you want these to be individual workbooks, individual workbooks?
So let's come back to Workbook Split. This time I'll say Split to Workbooks.
Please enter the target folder.
C:\aaa\ great and Execute Split Again, amazingly fast.
And if we come back here to our folder you see that I now have 27 new workbooks.
Each workbook has the records from that customer.
Awesome awesome tool here WBMerge now offers WB split.
check down in the YouTube comments for a link to that product.
If you have to do this routinely. Boy, what a great product.
Check out my book MrExcel LX the Holy Grail of Excel Tips.
Click that "I" in the top right hand corner.
Hey if you like the tips in this video please please click Subscribe. And ring that bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
