Synthetic merged-cell report flattened: nine rows in, five records out, every total reconciled

The invented report

This is a made-up report with invented products and figures. In the original, each region name is one merged cell spanning the rows of that region, so the name is stored once. Subtotal lines, a blank spacer row and a grand total sit inside the data area. Below the header there are nine rows.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

row | Region (merged)       | Product | Units | Value
1   | North (3 rows merged) | Anvil   | 4     | 120.00
2   |                       | Hinge   | 10    | 25.00
3   |                       | Latch   | 6     | 33.00
4   |                       | Subtotal North | 20 | 178.00
5   | (blank spacer row)
6   | South (2 rows merged) | Anvil   | 2     | 60.00
7   |                       | Latch   | 9     | 49.50
8   |                       | Subtotal South | 11 | 109.50
9   | Grand total           |         | 31    | 287.50

The hazard in a quick unmerge

Unmerging alone puts North only in the first row of its block and leaves empty cells below, because Microsoft says the data in a merged cell moves to the left cell when it is split. Sorting that table would separate Hinge and Latch from their region. The label must be repeated on every row it covered, and the subtotal and spacer rows must leave the data.

The flat table

One header row, no merged cells and one record per row, with the region repeated.

• Five records, one per product line, each carrying its own region.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

Region | Product | Units | Value
North  | Anvil   | 4     | 120.00
North  | Hinge   | 10    | 25.00
North  | Latch   | 6     | 33.00
South  | Anvil   | 2     | 60.00
South  | Latch   | 9     | 49.50

The reconciliation

Original rows must equal records plus removed rows, and totals must match. The removed rows are listed by type so nothing disappears unexplained.

• Original rows 9 = records 5 + removed 4 (one spacer, two subtotals, one grand total).
• Units: records sum to 4 + 10 + 6 + 2 + 9 = 31, equal to the grand total 31.
• Value: records sum to 287.50, equal to the grand total 287.50; North 178.00 and South 109.50 equal the original subtotals.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

check                         | original | flat sheet | result
record rows                   | 5        | 5          | match
removed: spacer               | 1        | 0          | listed
removed: subtotal lines       | 2        | 0          | listed, recomputed: 20/178.00 and 11/109.50 match
removed: grand total          | 1        | 0          | listed, recomputed: 31/287.50 matches
all rows                      | 9        | 5 + 4      | match

The label sample

Totals cannot reveal a label put on the wrong row, so a sample compares labels record by record. In a real job at least twenty records are compared; with five records here, all five are.

• Anvil/North, Hinge/North, Latch/North, Anvil/South and Latch/South each carry the label that covered them in the original.

Use it to specify an enquiry

If your report has one repeating layout and up to 5,000 data rows, the flattening job is from £145 (an untested proposal, with the final price confirmed after we see the layout description), accepted by checks like these, with payment after your sign-off. It does not correct figures, build a pivot or deal with labels whose rows nobody can identify. Send a description of the layout and the row count, never the report.