Combine (Append) Tables, Sheets, Files to Merge a Master List

DigDB

Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

Features | Testimonial | Download ^{free 15-day trial} | Purchase | FAQ | Contact

Combine (Append) Tables, Sheets, Files to Merge a Master List

This operation allows you to combine many tables by appending them together to form a single master table. Some people call it table merge - but we prefer to call it append. The simplest append is to append table of the same structure:

The above tables are of the same structure because they both have the same column headers. To append them is to simply stick them together under one header row. You can do this manually via copy and paste. What DigDB allows you is to do this in 1-click over many tables in many sheets and files.

Sometimes the tables may differ slightly. For example, the order of the columns may be different, or some column headers may be missing.

Appending the above 3 tables by matching column headers should produce a table like this:

If your tables do not have column headers, you can also just append one table on top of another. That is the simplest way of appending tables.

You can still append these tables by manual copy and paste. But it will be error-prone because you have to align and match the column headers and scroll up and down. DigDB allows you to append tables in just one click. In the end, a master table will be created in a new sheet and it will be value-only.

Append Multiple Tables into a Single Master Table (within a file)

If you have multiple files, see the next demo below on how to combine those files into a single file. Once everything is within a single file, you can proceed to append tables:

Invoke 'DigDB->Table->Append Tables in this File...'

Practice file - combine-tables-sheets-demo.xls (16k)
Click 'Add' 'All Sheets' to add each sheet's data area to the appending table pool.

Note that you can choose to append by column (field) header or just append one table on top of another.
Each entry in the 'Tables to Append' box is a sheet's data area. A sheet's data area is formed by its top and bottom non-empty rows and its left-most and right-most non-empty columns.
DigDB will treat each sheet's data area as a single table.

You can examine each table entry by clicking on it. The table's sheet will be brought to the front, and the table range will be highlighted. If it is not what you want, click 'Remove' to remove it from the appending pool. To make an arbitrary area selection, use the range selector: click in it, go to a sheet, select the area, then use the lower '<< Add' button to add to appending pool.
After you are done with table selections, click OK to start the appending. The appended table will be a master table in a new sheet.

A new column which contains the source sheet name and the range of the rows will be inserted. If you don't need it, simply delete it. But you may use sheet name to mean something, so you may want the sheet name to be included in the master table. If you only need the sheet name, use the DigDB->Column->Split function to carve out the sheet name portion.

Combine Multiple Excel Files into a Master Table

If you data is stored in many Excel files, and you need to combine them and append all the sheets into a master table, then you need to do this:

Invoke 'DigDB->Table->Combine Files...'
Click 'Add Files...' button, a file selector window will be opened to allow you to select and add Excel files to the file pool.
Select Excel files

Note that in the file selector, you can select multiple files by pattern match file names, for instance, select all Excel files with names like '* 2004.xls' - just click the 'Find' button, you will have a lot of options.
Click 'Open' in the file selector, the selected files are added to the file pool.
Click OK to combine the files. A new workbook will be created, and it copies over by value all the sheets from all the files you selected. You can then invoke 'DigDB->Table->Append...', and repeat the previous demo above to append all sheets into a master table.

Need more?

Browse the complete list of DigDB's powerful features listed on the front page.
If you see errors or things not working, check out our error reference page.

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.