Consolidate Department Sheets by Matching Labels in OpenOffice Calc

Combine two department tables by their labels even when the rows and numeric columns are arranged differently. A position-based sum would silently pair the wrong cells in this example, so both row and column labels matter.

Enter two differently ordered tables

Use a new Apache OpenOffice Calc 4.1 workbook. Create sheets named DeptA, DeptB, Snapshot and Linked. Keep both output sheets completely blank. Enter these exact tables starting at A1 on their named sheets; all quantities are integers.

DeptA.A1:C4:

Item	Jan	Feb
Paper	10	20
Ink	5	7
Clips	8	6

DeptB.A1:C4:

Item	Feb	Jan
Ink	3	4
Paper	12	9
Tape	2	1

Save a clean ODS copy. Paper’s January result must be 10+9, not 10+3. That single check catches an accidental positional consolidation.

Create a label-matched snapshot

1. Choose Data > Consolidate. In Function, choose Sum.
2. In Source data range, select or enter $DeptA.$A$1:$C$4 and click Add. Add $DeptB.$A$1:$C$4 separately. Confirm the Consolidation ranges list contains exactly those two entries.
3. Set Copy results to to $Snapshot.$A$1. The target is the upper-left cell, not a required output rectangle. Do not target either source table.
4. Click More. Under Consolidate by, select both Row labels and Column labels. Leave Link to source data unchecked for this first run.
5. Click OK. Locate rows and columns by their labels and compare against the following values; the verification does not depend on label order:

Item	Jan	Feb
Paper	19	32
Ink	9	10
Clips	8	6
Tape	1	2

The January column totals 37 and February 50; the combined total is 87. These are manual control totals, not extra rows that the consolidation is required to create. Clips and Tape occur in only one source each and must still appear. Labels must be included in the source ranges and spelled identically to match.

Compare snapshot and linked results

Repeat the consolidation with the same two source ranges, both label options and Sum, but set the destination to $Linked.$A$1 and select Link to source data. Check the range list carefully: do not add a source twice. The linked output can occupy more rows than the compact snapshot because Calc inserts source references and hides their rows beneath summary controls. Leave generous blank space and compare visible summary labels rather than assuming A1:C5 is the whole linked result.

Now change DeptB.C3 from 9 to 11. This is Paper’s January value. Snapshot should still show Paper/Jan 19 because it holds the earlier results. Linked should show Paper/Jan 21, with January totaling 39 and both months totaling 89. The guide documents that linked references update when source values change. Expand the linked outline to inspect reference rows, but do not sum both those rows and their summaries.

To refresh the snapshot, select its destination in Data > Consolidate again, confirm both original ranges and label choices, and run the operation with Link to source data off. Expect Paper/Jan 21. If reusing a sheet for a differently sized result, clear the old result area first or use a new empty output sheet to avoid stale trailing cells.

Growth and recovery

Restore DeptB.C3 to 9. Add Tape, 5, 1 in DeptA.A5:C5. On a new blank output sheet, run consolidation with DeptA’s range expanded to A1:C5 and DeptB still A1:C4. Keep both label options on. Tape now totals Jan 6 and Feb 3; the month totals are 42 and 51, overall 93. A previously configured fixed source range does not establish automatic inclusion of a new row.

The consolidation ranges and destination are saved with the document. Save, close, reopen, and inspect them before rerunning. Keep the clean copy if you want to restore the starting fixture; do not depend on Undo after reopening. For linked output with changed bounds, rebuilding on a new empty sheet is safer than assuming its previously generated references expanded.

Troubleshooting and limits

Separate rows for Paper and Paper with a trailing space are a label-quality problem. Remove accidental spaces in the source, then rebuild; do not merge output cells to hide the discrepancy. Wrong month totals suggest Column labels was omitted, or the source selection excluded the first row. Wrong item totals suggest Row labels was omitted. An unexpectedly doubled total often means a source range was added twice.

This example uses Sum on numeric measures. Average, Count and other functions answer different questions; an average of already-averaged department numbers is not necessarily a combined employee-level average. Use raw observations and explicit weights when that distinction matters.

The official Apache OpenOffice 4.1 Data Analysis chapter documents consolidation, matching labels, saved ranges and linked-output reference rows. Desktop dialog positioning and Mac shortcuts can differ. No native run or fixture screenshot is claimed here; these independently derived totals are the acceptance checks for your installed build.