The Combine Worksheets wizard joins multiple spreadsheets into a single large table and places records according to their headers. It doesn't matter whether the tables structured identically or differently. You can pick out the columns you want to merge if needed, so that unnecessary data are not copied to a new file.
Since the add-in processes table columns, all sheets you want to join must have headers.
If there are filtered, grouped, or hidden cells in your tables, they will be ignored.
To run the add-in, click the Combine Sheets icon on the Ablebits Data tab in the Merge group:
Here you can see all the files that are currently open in Excel. You can pick out the worksheets you would like to join by ticking off the checkboxes next to their names:
The add-in automatically pulls all data from the chosen worksheets. To change the highlighted area, select the sheet name in the list and click on the Select range icon under the Ranges column. It will open this sheet, and you will see a dialog box that allows you to pick the necessary cells:
The buttons at the bottom of the wizard's window will also help you deal with a big number of files:
Click Next to go to Step 2.
The columns you choose on this step will be joined together based on their headers and placed to a new location.
Here you can see a list of column headers from all the selected worksheets and the total number of times a given label occurs in the tables. E.g. if you choose 5 files and each has a column named "Fruit", you will see 5 in the Number of occurrences field for this column.
To exclude some records from the results, unselect the column in the list.
If you want to combine data from all columns, keep all the checkboxes ticked off.
Click Next to continue.
Or click Back to return to a previous step and delete, add, or pick out other worksheets.
This step lets you define the way to copy and paste records to the merged columns:
Press Next to proceed to Step 4.
Select the destination for the combined data from one of the following options:
Click Combine and get the data from the same columns merged into one Excel table.
Responses
Hello,
Is there a way to know where the data came from? For example to include a file path
Thank you
Hello Tony,
Thank you for contacting us. Sorry, we don't quite understand your question. Could you please specify? Thank you!
I have two sheets I need to combine. Both sheets have a column that is a row identifier. The rows are not exactly the same in both sheets. I need to combine the sheets but need to match according to the row identifier.
for example:
sheet one sheet two
bhid barcode color bhid barcode color
1234 neo2342 black 1234 neo2342 black
4564 neo4876 red 6578 neo6543 yellow
5648 neo9999 red 5648 neo9999 red
Hello,
Thank you for contacting us.
It looks like our Combine Sheets add-in works the way you need. It can merge your data from several worksheets into one based on the headers. The add-in is available as a part of our Ultimate Suite that contains 60+ tools to simplify your work in Excel. You can install it in a trial mode to make sure Combine Sheets and other add-ins work for you: https://www.ablebits.com/files/get.php?addin=xl-suite
If this is not exactly what you're looking for, please send us a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Just about 10-20 rows will be enough. We'll look into your task and try to find the best tool to solve it.
One I have combined different tabs to one MASTER tab, how can I update (or refresh) the MASTER tab if I add a row to one of the tabs that it is linked to?
Thank you for your question, Anne Marie.
I’m afraid there’s no way to refresh the data automatically at the moment. You need to run the add-on each time you want to combine sheets.
Hi,
I combined a bunch of sheets but the total number of rows is not as many as expected. I think there may be a command to ignore duplicates? How do I make sure that the duplicates are included as they are the same but are not duplicates.
Thanks
Hi Ben,
Thank you for contacting us.
Since our Combine Worksheets preserves duplicates while joining your sheets, we need to know some more details to understand the problem you faced better. Please check your Inbox and provide us with the requested information.
Thank you.
Hi there! This is going to save me so much work. Thank you.
Is there a way to preserve formatting of the destination sheet like borders and fixed columns?
Many thanks
ps referring to the "Combine sheets"
Hi Diana!
Thank you so much for your kind feedback, we appreciate it a lot.
Unfortunately, the add-in does not preserve formatting. The thing is that this feature would significantly slow down the combing process, so we’ve chosen speed instead of formatting. However, we are trying to find a way to implement it without losses in the add-in’s performance. So we are working on it 🙂
Thank you.
I have a large amount of worksheets to combine. When I use the software it prematurely opens the combined results, which only contains about half of the sheets.
Hi Kevin,
Thank you for your comment.
The problem may be in Excel limitations.
The questions are: how many books you are trying to combine? And how many rows and columns does each book have? And where do you pick to place the results: to a new worksheet or to a new book?
Our products do not impose any additional limitations, so the number of rows and columns in the resulting table is defined by the version of Excel you have, please find them here.
If this is not the case, please contact us at support@ablebits.com and we will help you asap.