The idea
Whenever you combine two lists of numbers, you decide, silently or not, what lines up with what. There are two answers.
By position: the value in row 3, column 2 of one block belongs with row 3, column 2 of the
other. This is the right answer when the blocks come from the same template, like the three
channel reports that all list six categories in the same order for Jan, Feb and Mar. Excel's
SUM over cells in the same place does exactly this, and so does the 3-D reference
=SUM(Web:Marketplace!B3) across sheets.
By label: a value belongs with the other values that carry the same product id or category name, wherever they sit. That is what you need when one month has a product the other lacks, or when the exports are sorted differently.
Figure 1
Two months of Kitchen units, in different row orders
| product_id | units |
|---|---|
| 141 | 1 |
| 142 | 2 |
| 146 | 7 |
| product_id | units |
|---|---|
| 146 | 1 |
| 143 | 1 |
| 142 | 3 |
| product_id | units |
|---|---|
| 141 | 2 |
| 142 | 3 |
| 146 | 10 |
| product_id | units |
|---|---|
| 141 | 1 |
| 142 | 5 |
| 143 | 1 |
| 146 | 8 |
- Two exports from the marketplace, with the same two columns. September is sorted by product id. October comes newest id first, and it has product 143 where September has 141.
- Consolidate by position adds row 1 to row 1, row 2 to row 2, and so on. Row 1 is product 141 in September and product 146 in October. It adds them anyway and keeps September's label.
- Row 2 adds September's 142 (2 units) to October's 143 (1 unit) and calls the result 142: 3 units. The true total for 142 is 5. Position cannot know the rows are in a different order.
- Consolidate by label looks the key up first. Product 142 has 2 units in September and 3 in October: 5, wherever those rows sit.
- Products that appear in one month only keep their rows: 141 only in September, 143 only in October. The result has four products, not three.
Why it exists
Real exports are rarely identical. One report is sorted by id, the next by date. A product that sold in September has no row in October. Two people maintain two price lists and add rows in different places. If you assume position and the layout has drifted, you do not get an error. You get plausible numbers under the wrong names.
Think of the monthly close. Finance gets a file from each channel, pastes them together, and sends one total to the board. If a single file had a row inserted at the top, nothing in the sheet complains, and the error stays in the report until somebody compares it with a source. The cost is not a failed formula but a wrong decision that nobody questions, because the numbers look fine.
That is also why this chapter has more than one tool. Position is quick and fine when you control the template. Label survives changes in layout. A master table goes further: it stops the matching problem from coming back every month.
How it works under the hood
Consolidate by position
Everything depends on identical layout: same rows in the same order, same columns in the same
order. In Excel, Data → Consolidate with Sum does this when you leave "Top row" and "Left column"
unchecked, and a formula like =SUM(B3, B12, B21) does it by hand. It is fast, and it is
blind. If one source gains a row, every row below it is now compared with the wrong partner.
Consolidate by label
Here the key decides. In Data → Consolidate, tick Left column (and Top row if columns also
need matching) and Excel groups the rows by their label before adding. With formulas, a SUMIF for
each block does the same: for the product id in column D, add the units from the September block
where the ids match, plus the units from the October block where the ids match. The two blocks
can be in any order, and a product missing from one block simply contributes 0.
One limit: you need a list of labels to start from. Build it from the union of both blocks, with each id once, or the product that sold only in October never gets a row.
Master table: stack, then summarise
The strongest approach is to stop matching altogether. Put every row of every source into one list, one under another, and add a column recording where each came from.
Figure 2
Stacking the exports into one master table
| month | product_id | units |
|---|---|---|
| Sep | 141 | 1 |
| Sep | 142 | 2 |
| Sep | 146 | 7 |
| Oct | 146 | 1 |
| Oct | 143 | 1 |
| Oct | 142 | 3 |
| product_id | units |
|---|---|
| 142 | 5 |
| 146 | 8 |
- Stack the blocks one under another and add a
monthcolumn saying where each row came from. Order no longer matters, and new months go underneath. =SUMIFS(C:C, B:B, 142)adds the units of every row whose product_id is 142, from any month: 2 and 3 make 5.- Add a month criterion and the same table answers "142 in October only". One list, any summary: a PivotTable builds all of them in a few clicks.
Now "units for 142" is a SUMIFS over one list, or a PivotTable with product_id in Rows and
month in Columns. Nothing relies on layout. When November arrives, you paste its rows at the
bottom with Nov in the new column, and every summary is already waiting. A Table (Ctrl+T)
extends the formulas and the pivot's source on its own. The source column matters. Without it,
you cannot ask for a single month, and you cannot tell two identical rows from two real sales.
Which one to use
Ask what could differ between sources. Identical template every time, and you own it: position. Same fields, rows in any order or with gaps: label. A source that will keep growing, or questions you cannot predict: master table. The same question applies to comparing two lists, such as old and new price lists, where the whole task is to find what appears in only one of them.
Common misconceptions
"If the grand totals match, the consolidation is right"
The totals match even when the rows are mismatched. Adding by position above still gives 15 units in total (2 + 3 + 10), the same as the correct 15 (1 + 5 + 1 + 8). Only the per-product numbers are wrong, and the grand total hides that. After consolidating, check two or three individual products against their sources by hand.
"Sorting both blocks first makes position safe"
It helps only until a product is missing from one block. Product 143 exists in October only, so once both blocks are sorted by id, September's 141 is added to October's 142 because they hold the same row number. Sorting aligns the rows only when both blocks list exactly the same keys. When they might not, match by label.
"Consolidating once means the result stays up to date"
A consolidation made with the Data → Consolidate dialog is a snapshot unless you tick "Create
links to source data". Formulas (SUMIF, SUMIFS) and PivotTables update with their sources,
the second one after a refresh. If a report has to be rebuilt every month, use formulas or a
master table, not a one-time paste.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. Position never matches anything. That is what consolidating by label does. By position, the rows are added blindly.Try another answer.
Not quite. No error appears, which is what makes position consolidation dangerous. Excel adds the two numbers and keeps the first block's label.Try another answer.
Right. Position adds whatever sits in the same place and keeps the first block's label, so 142 gets 143's units. The total of all products still looks fine, but the per-product numbers are wrong.
Not quite. The label comes from the first block, September. The October label is not used at all.Try another answer.
Lesson done: it is ticked on the map.