Consolidation Assistant

A data related add-in product for Microsoft Excel

Do you have these problems:

• Multiple worksheets in different workbooks that you want to condense into one workbook?
• Need to extract multiple ranges from a number of workbooks?
• Want to consolidate data from multiple worksheets into one worksheet?

The Consolidation Assistant can solve all of these problems and more!
 

The Consolidation Assistant is designed to help you consolidate data from multiple workbooks or from multiple worksheets.

The following is the Consolidation Assistant's main menu which illustrates its many features:  

Workbook consolidation can be done several ways:

• Extract up to ten ranges from any worksheets in a group of workbooks
• Combine worksheets from a list of workbooks
• Select a set of workbooks and specifying a range name that identifies the data range to be extracted.

The Consolidation Assistant allows you to extract up to 10 ranges from all selected workbooks and put the output on a single worksheet.  You can specify different numbers of rows and columns for each range.   Each range extraction can be transposed on the output worksheet. Blank rows can put between extractions. The source workbook is listed beside the extraction to insure identification of the data.  If you do not want the data extracted to just one worksheet then the data can be extracted to individual worksheets named after the source workbooks.

Combining worksheets from a list of workbooks is a two step operation. First, the Consolidation Assistants helps you to create a list of files and their worksheets.  For those sheets you wish to consolidate, you just type the name to use in the consolidated file to the right of the existing name.  Once you have done this, just select the range containing the new sheet names and run option two on the above menu.  Blank cells in the selection will be ignored.  When the consolidation is finished you will end up with a workbook containing each of the sheets that you specified.   You can then use other features in the Consolidation Assistant to either combine these sheets together or to extract specific ranges from each of the sheets.

The Consolidation Assistant has several features that allow you to conveniently view your data. One freezes panes on all selected sheets.  Another sets the same cell on all selected sheets to the upper left corner. And at the same time sets the zoom to the same setting on all the worksheets.

Once you have the data consolidated into one workbook you can use the search features of the Consolidation Assistant to extract ranges from selected worksheets:

For example, if row 50 is the total row for a group of sheets you can extract this row from those sheets. If the total row is identified by the words "Total Row" but is not always the same row one can use the search for matching text and extract the total row values.

If you are consolidating CSV or text files, options in the Consolidation Assistant allow you to:

• Consolidate by selecting files in a file open dialog or
• Create a list of files in a directory and its sub directory and then select the ones you want to consolidate.

CSV and text file consolidation allow you to:

• Consolidate each file to its own worksheet
• Consolidate all files to one worksheet
• Consolidate all files to one worksheet and list the source file on each row