One of the common daily tasks for Excel users is to combine several Excel worksheets into one. In this article we’ll show you how Excel Compare utility does this in a few clicks only.
Let’s imagine you have 3 lists with auto parts, saved in a form of Excel worksheets, received from your colleague. You need to combine them into single list for future processing.
If you have 3 small lists, you can just copy them manually and paste into one worksheet. But if you have 50 worksheets and 1000+ records in each list, this operation will take a time. If lists contain duplicate records – manual comparison is not a good solution – you can miss important information.
Errors in spreadsheets cost companies millions dollars. Do not lose your time and money on the mistakes in the workbooks you are using.
So, on this small artificial example we describe how xlCompare merges sheets and excel files into one copy.
xlCompare is fast and easy to use Excel merge utility. Just in several mouse clicks you have your merge job done. You will not spend you time learning software options for basic operations.
Open your worksheets to begin
In this example all lists with auto parts are on the different sheets in the same workbook. So, we just drop the workbook into Excel Compare application from Windows Explorer.
We are going to merge all parts listed on the Sheet2 and Sheet3 into worksheet Sheet1.
Excel Compare has Portrait and Landscape layouts. By default we are using Portrait, when worksheets are tiled vertically. Left worksheet contains MINE worksheet and right worksheet contains THEIR worksheet.
If sheet has many columns with data, it may be useful to change layout to Landscape, to have more data on the screen. So, we’ve changed layout to Landscape. All images you see on this page are from the Landscape layout.
Please, note: this feature is optional. We’ve described it just for your information. It is not used in the Merge process.
Now we go ahead to merge the worksheets.
Press brown Merge button on the Main Window
It opens Merge Worksheets Wizard.
This is simple window where you should select the following:
We plan to merge Sheet2 and Sheet3 into Sheet1. So, on the bottom of the Wizard we select Sheet1 as a result of the Merge operation. And click on the check box on the header:
It checks all items in list.
This means that checked worksheets (Sheet2 and Sheet3) will be merged into result worksheet.
That’s all we need.
Press Merge button on the bottom of the Wizard
There will be no comparison results and no difference explorer window. Excel Compare gives you results silently and immediately. This command is for mass merge operation.
As you see, data from the Sheet2 and Sheet3 was copied into Sheet1.
Sheet2 and Sheet3 has rows above the list with auto parts. They are also present in the Sheet1, so they were not copied.
Only modified data was copied to the target worksheet.
Let’s count how may clicks we need to merge several excel worksheets into one:
So, in 3 clicks and few seconds we’ve combined 3 Excel worksheets into one. In the same 3 clicks you can merge 50 worksheets with 10000+ rows on the each one!
What about duplicated rows. If one part is present in two worksheets? In this case xlCompare identifies same row and copies only modified cells. Row is not duplicated in the target worksheet.
How to identify inserted rows?
Good question. You want to know which record were added into my original list by Excel Compare. In the example above we’ve got our target sheet filled with rows from other sheets, but it is too hard to distinguish them. In the large amount of rows it is almost impossible.
We enable option Mark Changes when Merging. In the Options | Merge tab. It was disabled, so all merged rows appear with source formatting.
Let’s open this workbook again and to the same operation to merge 3 sheets into 1.
All inserted rows appear in green fill color. Background color identifies inserted rows within other rows in this worksheet.
How to change formatting on these rows. Select all rows filled with green color and change formatting on the Format tab on the Ribbon. It has interface very similar to Excel application. You can change:
Select records to be merged
Let’s imagine another situation – you need to select data, which should be inserted into your worksheet.
In this case automatic merge command is not applicable.
We will merge these lists with auto parts manually.
Again, drop file into Excel Compare window. Now we are using Portrait orientation, to have rows on the same level.
In the left panel we select MINE worksheet Sheet1. It will receive the data and on the right side – Sheet2. Rows from the Sheet2 will be copied into target sheet.
Use Compare button on the Main Window. It opens Comparison Wizard. In the Comparison Wizard we select pairs of worksheets to be compared.
This example is relatively simple – we have one pair of worksheets: (Sheet1, Sheet2).
Now press Compare button on the bottom of the Wizard to see color coded difference between worksheets.
This is Comparison Report. We will use it to merge cells between worksheets.
All unique rows are marked with colors:
There are no duplicate rows in our lists, so all rows we have on the worksheets are marked with red and green fill color.
For example – we need only first row – Disk Brake for Toyota.
Click on the merge button (blue arrow icon) on the row headings. This moves row into another worksheet.
Merge icon in the header is changed to the UNDO icon. So, you can undo this merge operation in case of mistake.
This way takes more time, and requires some attention, but you have full control over the data you merge.
After you’ve finished merging – click on the Close Report button and exit from Comparison Report mode.
Merge lists, which are in the different workbooks
It is similar to merging several excel worksheets into one sheet.
We’ve divided this list into 2 parts and will merge 2 workbooks.
Drop target file into left panel – it will receive new rows – result of the merge command. We drop other file on the right side – this is source for the new(inserted) records.
In this case Merge button on the Main Window doesn’t show Merge Wizard. It just moves data from one workbook into another one.
So, now in single click without selecting any comparison options we’ve got the results.
Part List 2.xlsx has being merged into Part List 1.xlsx.
Color formatting is optional. Turn option Mark Changes when Merging ON, to enable it.
Excel Compare is powerful excel merge tool. Comparison Engine allows to implement all possible types of merge operations you need on the daily basis.
If we know all differences between worksheets – we can merge them in all ways.
Armed with this merge utility you will greatly simplify your work with Excel worksheets, and make it more effective.
Excel Compare is a good investment, which saves you time and money