Assemble the workbook

Assemble a working sheet beside a preserved source, mark repeated orders with a key that settles what "the same order" means, and report what could not be priced instead of quietly pricing it at zero.

Cedar Ridge has to decide how to buy equipment next year. Before anybody can compare two quotes, somebody has to answer a smaller question: what did we actually order last term?

The answer lives in twelve rows entered by three people over five weeks, and it is not clean. Two orders were entered twice, once when the order went out and again when the invoice arrived. One supplier name carries a trailing space nobody can see. One item code is not in the rate table at all. And the rate table itself has two rows for the same code at two different prices, because somebody repriced in August and nobody removed the March row.

Deleting repeats, retyping the supplier, guessing a missing price or choosing one conflicting rate would hide decisions; a guessed price would enter the reported total as fact. Keep the source intact. Add working columns for each rule and a cleanup log for the policy calls the workbook cannot settle, so another person can review both row and reason.

Keep the source, work beside it.

The first sheet is what was entered. It is never edited. The second sheet reads from it and adds columns. If a judgement turns out to be wrong, the evidence for the right one is still there, and anybody who wants to reconcile against the entry system can.

The extra sheet keeps the workbook auditable.

A record key settles what "the same" means.

Two rows are duplicates when they agree on the fields that define an order. Which fields? That is a decision, and writing it as a formula is what makes it a decision rather than an impression:

=F2 & "|" & C2 & "|" & D2 & "|" & E2

Supplier, item, quantity, date. Change any one of them and it is a different order. A key of supplier and item alone would say that two Caldwell orders for cones — thirty in June and seventy-five in July — are the same order, which they are not.

The separator matters. Without one, supplier A1 plus item 00 and supplier A plus item 100 both start with A100; with a pipe they start A1|00 and A|100. The supplied workbook has no pipe in these key fields. For other data, escape any delimiter that can occur in a field or use an unambiguous structured key; the separator alone is not a universal guarantee.

Count the key, then keep the first.

H2  =COUNTIF($G$2:$G$13, G2)          how many rows share this key
I2  =IF(COUNTIF($G$2:G2, G2)>1, "drop", "keep")

The second one is worth reading twice. $G$2:G2 grows as it is filled down: on row 2 it looks at one cell, on row 7 at six. So it counts how many rows at or above this one share the key. The first copy sees itself only and is kept; every later copy sees an earlier one and is dropped.

Marking every row of a repeated pair as a duplicate is the common mistake, and it deletes both copies of an order that genuinely happened.

Let the lookup fail.

=VLOOKUP($C2, Rates!$B$2:$C$6, 2, FALSE)

When the code is missing this returns #N/A, and #N/A is the correct answer. Wrapping it in IFERROR(..., 0) produces a workbook in which eighteen items cost nothing, and nothing downstream will ever mention them again.

A price that worked is not always a price you can defend.

Item code 200 appears twice in the rate table, at 6.10 and 6.95. The lookup finds the first one and reports 6.10 without complaint. That is not an error and it is not reliable, so it needs its own state:

=IF(ISNA(J2), "code not in the rate table",
   IF(COUNTIF(Rates!$B$2:$B$6, $C2)>1, "duplicate code", "matched"))

Three outcomes, not two. Priced and trustworthy, priced and contested, not priced at all.

Reconcile, so nothing can vanish.

Two reconciliations run here. First, the three pricing groups must add back to rows kept.

=O6 + O7 + O8 - O5      must be zero

The second breaks the kept rows down by supplier and requires the same thing. That one exists because of the trailing space. A supplier name that was never tidied matches none of the three named supplier criteria in this workbook, so it drops out of those per-supplier counts. Their total then falls short of rows kept. The reconciliation makes that net gap visible.

This is the general principle. A check is worth having when there is a specific way for the sheet to be wrong that the check would catch. "The parts add up to the whole" detects a net mismatch when a row belongs to no group, is counted twice, or a criterion matches nothing. Offsetting mistakes can cancel, so a zero difference is necessary evidence, not proof that every row was classified correctly.

Two of the twelve rows, as entered:

Ref    Supplier as entered   Item   Qty   Date
P-01   Ridgeline Supply       100   120   2026-06-02
P-06   Ridgeline Supply       100   120   2026-06-02

Same supplier, same item, same quantity, same day. P-06 was entered when the invoice arrived. It is one order recorded twice.

Two others:

P-07   Caldwell Kit           100    30   2026-06-23
P-11   Caldwell Kit           100    75   2026-07-07

Same supplier, same item — and two real orders, two weeks apart, in different quantities. A key of supplier and item would merge them and lose seventy-five cones.

And one more:

P-05   "Brookfield Sports "   200    45   2026-06-16

The trailing space is invisible in the cell. It is not invisible to a count:

=COUNTIFS($F$2:$F$13, "Brookfield Sports", $I$2:$I$13, "keep")

Untidied, that returns 3 where the answer is 4, and the sheet under-reports Brookfield by forty-five items with no sign that anything happened.

The rate table, which is the other half of the problem:

Item    Code   Price   Note
Cones    100    4.25
Bibs     200    6.10   Priced in March
Nets     300    9.00
Bibs     200    6.95   Repriced in August
Balls    400    3.40

Code 200 has two prices. Three of the ten kept orders are for code 200, and because they are large orders that is 1,006.50 of the 2,669.25 that priced at all — over a third of the value rests on a table that contradicts itself.

The assembly checks, once the columns are built:

O2   Rows in the source                              12
O3   Rows that repeat another exactly                 ?
O4   Rows to drop                                     ?
O5   Rows kept                                        ?

Two pairs repeat. Predict O3 and O4 before reading on — they are not the same number, and the difference is the whole point of the deduplication rule.

O2   Rows in the source                              12
O3   Rows that repeat another exactly                 4
O4   Rows to drop                                     2
O5   Rows kept                                       10

O6   Kept rows that priced cleanly                    6
O7   Kept rows priced from a duplicate code           3
O8   Kept rows with no matching code                  1
O9   Coverage: the three groups minus the rows kept   0

Here is the value block as most people first write it:

O10  Total value    =SUM(L2:L13)

One number. It is also #N/A, because one of the twelve rows could not be priced and an error in a range propagates through SUM.

The first instinct is to remove the error:

O10  =SUMIF(I2:I13, "keep", L2:L13)      still #N/A
O10  =IFERROR(SUM(L2:L13), 0)            now 0, which is worse

Neither of those is the fix, because the problem is not the error. The problem is that one number cannot describe three different situations. Split it:

O10  Value that priced cleanly           =SUMIFS(L2:L13,I2:I13,"keep",K2:K13,"matched")
O11  Value priced from a duplicate code  =SUMIFS(L2:L13,I2:I13,"keep",K2:K13,"duplicate code")
O12  Quantity that could not be priced   =SUMIFS(D2:D13,I2:I13,"keep",K2:K13,"code not in the rate table")

O10 reports 1,662.75 from clean matches. O11 reports 1,006.50 from a contested rate; the same 165 items would cost 1,146.75 at the August price, 140.25 more. O12 reports eighteen unpriced items as a quantity, not an invented value. The three disjoint groups replace a misleading single total.

A colleague sends you an assembled workbook. Every cell has a value, nothing shows an error, and the total is 2,669.25. Here is how to find out whether it is true, in the order that finds problems fastest.

1. Check the reconciliations first. If the sheet has none, that is the finding — add them before looking at anything else. A nonzero result exposes a net gap. Zero is necessary, but offsetting classification errors can still cancel, so inspect the row rules too.

2. Look for IFERROR in the pricing column. IFERROR(..., 0) can hide a missing price as a free item. No visible error does not prove the prices are right: a repeated rate-table code can also return a plausible but contested value.

3. Reconcile source, working and kept counts separately. Source and working sheets should each retain twelve order rows; the working sheet marks two drop and ten keep. A working sheet with only ten rows has removed source records from view, even if the retained-order count also happens to be ten. Check the row identities and the drop column before trusting the summary.

4. Test the duplicate rule in an isolated copy. Change one key field to a value that collides with no other order, then recompute. If the pair remains grouped, the key may omit that field; if the drop mark does not respond, inspect its formula.

5. Search the lookup table for repeated keys. =COUNTIF(Rates!$B$2:$B$6, $C2)>1 flags codes with multiple rates. A lookup can return the first price without warning, so a valid-looking value still needs this check.

6. Reconcile supplier counts with rows kept. A nonzero gap needs row-level investigation. A stray space, unlisted supplier, blank, double count or wrong total can cause it.

The general shape: a reconciliation gives a reported value a constraint that could contradict it. It is one test, not a guarantee of correct formulas or row identity; trace suspicious totals back to source rows and rules even when the differences are zero.

Assemble the sheet. The source is preserved and locked; everything you add goes in a column beside it.

Work in order: tidy the supplier, build the key, mark repeats, price rows, then reconcile. The three editable decision cells R2:R4 sit beside the supplied issue notes in Q2:Q4.

You are ready for the comparison when you can do all of these.

• Say why the source sheet is preserved rather than corrected, and what that costs.
• Write a record key, and say which fields belong in it and why a separator is needed.
• Explain why the count of rows sharing a key and the count of rows to drop are different numbers.
• Say what IFERROR(lookup, 0) does to a missing price, and why #N/A is the better answer.
• Name the third state a lookup can be in, besides matched and missing, and what causes it.
• Write a reconciliation whose correct answer is zero, and say which specific error it catches.

Sign in to track your progress on this exercise.

The workbook is assembled when the source is intact, every judgement is a column somebody else can read, the repeats were found by a rule rather than by eye, the rows that could not be priced are reported as a quantity rather than absorbed into a total, and two reconciliations sit at zero.

Ten orders, 1,662.75 that can be defended, 1,006.50 that depends on an unresolved rate table, and eighteen items with no price. That is the ground the decision stands on.

Next: two quotes for next year. One has a fee and a low unit price, the other has no fee, a higher unit price and a delivery charge on every order. Which is cheaper turns out to depend on a number nobody knows yet.