11.5 Consolidating Tables

Fundamentals

By consolidating tables is meant that the data from several tables are collected into a single table. This process is most easily understood in the context of a concrete example: Let us suppose that you have four identically formatted Excel files with quarterly reports of certain sales figures, and you wish to create out of them a new Excel file with the annual figures. Or perhaps in the previous weeks measurement reports were created out of which you wish to determine the average values. For such tasks , or similar ones, the command DataConsolidate is useful.

Data consolidation is managed in a dialog in which several references to various cell ranges must be specified. These ranges may be located in the current worksheet, in other worksheets of the current workbook, or in external Excel files. In giving the names of files, wildcards such as [ Name *.xls]Sheet1!A1:C20 are allowed, in which case Excel reads the data from all Excel fields whose first four letters are Name .

Excel is less flexible in the selection of worksheets of a file. Here neither wildcards nor ranges (of the form ˜Sheet1:Sheet5 !A1:C20 ) are allowed. If data are to be consolidated from several worksheets, then all the worksheets must be listed separately, which can be a tiresome occupation .

Excel can execute a number of calculational operations (such as sum, average, variance) in the course of consolidation. The allowable operations are available for selection in the Consolidate dialog. The operation then holds for all fields in the consolidation range. It is not possible to calculate the sum of some fields and the average value of others.

Options

In the Consolidate dialog there are three option fields that normally are deactivated. Their effect can scarcely be guessed at from their short texts :

By activating Use Labels IN Top Row and Use Labels In Left Column a somewhat intelligent consolidation mode is activated: In creating the new table, Excel takes into account the label cells at the left and upper borders of the consolidation region. Excel is capable of correctly assembling the data even if the arrangement of the individual consolidation ranges differs among the tables. (However, it is assumed that all rows and columns are correctly and identically labeled.) If both options are deactivated, then Excel pays no attention to the labels, but simply carries out the calculations for corresponding cells.

By activating the option Create Links To Source Data Excel inserts into the new table countless references to the individual cells in the affected table. These references are then linked together with ordinary Excel formulas. Finally, Excel divides up the table in such a way that only the result cells are visible. This extravagance has an advantage: The consolidation table is continually updated, with every change in the source data reflected in the result table. But this involves two serious drawbacks: The time and memory requirements for this dynamic variant are enormous .

As is clear from the paragraph above, consolidation tables are static as a rule (unless the option Create Links To Source Data has been activated). Sad to say, there is no command, similar to that for pivot tables, by which data might be updated. The only possibility of maintaining current data consists in the repeated application of the consolidation command.

Tip	Excel can save the settings of the Consolidate dialog only once per worksheet. For this reason you should avoid having more than one consolidation table in a worksheet. Otherwise, you will have to input all the references anew upon each updating!

The consolidation command is seemly inflexible in its application, especially in carrying out complex operations involving several tables. You should therefore consider before using this command whether it might make better sense to work with ordinary Excel formulas, with which references to other tables are also allowed. However, you must allow for the fact that formulas with references to other files are not evaluated quickly. With extensive data sets you could run into time problems.

Altogether, the consolidation of data is a somewhat difficult and error-prone undertaking, one that should be used only by experienced Excel users. An automation of this process with suitable macros is therefore almost a necessity.

Example

In Figure 11-12 you may observe three windows : On the left are two (of six altogether) worksheets, in which measurements over six weeks have been entered. To the right is shown the consolidation table, in which the average values of the six measurements appear. This example application can be found on the CD-ROM, in the file Consolidate.xls .

Figure 11-12: Consolidation of measurement data

Consolidation via VBA Code

If your desires run in the direction of automating consolidation, then the macro recorder can assist you in achieving correct code quickly. For the example above it would look something like the following:

 Selection.Consolidate Sources:=Array( _     "'I:\Code\XL-2000\[Consolidate.xls]sheet1'!R4C2:R20C4", _     "'I:\Code\XL-2000\[Consolidate.xls]sheet2'!R4C2:R20C4", _     "'I:\Code\XL-2000\[Consolidate.xls]sheet3'!R4C2:R20C4", _     "'I:\Code\XL-2000\[Consolidate.xls]sheet4'!R4C2:R20C4", _     "'I:\Code\XL-2000\[Consolidate.xls]sheet5'!R4C2:R20C4", _     "'I:\Code\XL-2000\[Consolidate.xls]sheet6'!R4C2:R20C4"), _   Function:=xlAverage, TopRow:=True, _   LeftColumn:=True, CreateLinks:=False

The method Consolidate is applied to a Range object, which may specify the size of the target range or simply a start cell for the operation ([B4] in the example above). The meaning of the parameters should be clear from the example.

If you wish to set the Sources parameter dynamically, you may pass a field instead of the Array construct. The following lines show how the code can be modified so that it functions independently of path and file name:

 ' Consolidate.xls, Module1 Sub  ConsolidateDate  ()   Dim i&, arr$(1 To 6)   For i = 1 To 6     arr(i) = "'" & ThisWorkbook.Path & _              "\[" & ThisWorkbook.Name & "]" & _              "Sheet" & i & "'!R4C2:R20C4"   Next   [B4].Consolidate Sources:=arr(), Function:=xlAverage, TopRow:=True, _     LeftColumn:=True, CreateLinks:=False End Sub

Pointer

In the previous chapter a program was introduced that creates daily and monthly reports from measurements in the form of daily files. As well there appeared the issue of consolidating data from numerous Excel tables into one new table. However, there the command DataConsolidate was not used (too inflexible for this task), but the problem was solved , rather, through a relatively simple VBA procedure (see Chapter 10 under the heading "Monthly Report").