DigDB      Excel Add-ins      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:
    Combine (Append) Tables, Sheets, Files to Merge a Master List

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.

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

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

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

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:

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

    Combine (Append) Tables, Sheets, Files to Merge a Master List
    Practice file - combine-tables-sheets-demo.xls (16k)

  2. Click 'Add' 'All Sheets' to add each sheet's data area to the appending table pool.

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

    Note that you can choose to append by column (field) header or just append one table on top of another.

  3. 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.

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

    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.

  4. 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.

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

    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:
  1. Invoke 'DigDB->Table->Combine Files...'

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

  2. Click 'Add Files...' button, a file selector window will be opened to allow you to select and add Excel files to the file pool.

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

  3. Select Excel files

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

    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.

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

  4. Click 'Open' in the file selector, the selected files are added to the file pool.

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

  5. 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?


Features | Testimonial | Download | Purchase | FAQ | Contact (free forum)

Copyright © 1999-2007 All rights reserved

DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

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 Office Marketplace logo
Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.