» Consolidating Lists in Excel 2007

The following rules enable the consolidation of Lists using the Consolidate command:

The structure of the Lists must be identical.
The headings of all rows and the leftmost columns in the Lists must contain the same topic.
The number of columns and the number of rows do not have to be identical; nor does the internal order of the text.
The Lists must have a single row for labels, and a single column for labels.
The cells in the Lists data range (cells B2:E5 of List1 in the screenshot) must contain only numeric data.
Excel consolidates data by identifying corresponding text crossed between the header row and the leftmost column.




To consolidate Lists:

1. Select a cell within List1, press Ctrl+Shift+* or press Ctrl+A) to select the List, and then press Ctrl+F3 to define a Name to List1.
2. Repeat step1 and define a Name for List2 and a Name for List3.
3. Select cell A1 in a different sheet of the workbook, and select Data -> Consolidate (in Data Tools Group).
4. In the Reference box, press F3.
5. In the Paste Name dialog box, select List1, click OK, and then click Add to add List1 to All references box.
6. Repeat steps 4 and 5, and add List2 and List3 to All references box.
7. In Use Labels in, select the Top row and Left column checkboxes, and then click OK.



The consolidated Lists: