Excel join doubled your totals? Reconcile orders and payments step by step

01Decide between order-level reconciliation and line allocation

This exercise asks whether receipts reconcile with each order amount. It does not allocate a payment to a product line. Both sources may legitimately contain several records per order. Their amounts can be summed by order, but equal totals do not establish payment ownership. Confirm the same currency, tax, refund and date definitions first.

To add an owner to a detail record, use a verified reference key. To append similar transactions from files, follow header-based merging. Microsoft describes key-based merges and join choices; the arithmetic here is derived from the constructed inputs below.

02Two complete CSVs and the expected result for every order

These fictional records share one amount unit and an agreed settlement scope. Save the blocks separately as orders.csv and payments.csv. Both use Order and Amount to provide common fields for reconciliation. Line and Payment identify the individual source records.

Five order lines total 300; four payments total 295.01. The overall difference of −4.99 is the sum of reconciliation differences, not proof that every order is paid. An unexplained payment of 25 can partly offset an order of 30 with no matching receipt.

Order,Line,Amount
O-17,1,120
O-17,2,80
O-18,1,50
O-18,2,20
O-20,1,30

Order,Payment,Amount
O-17,P-1,100
O-17,P-2,100
O-18,P-3,70.01
O-19,P-4,25

03Why two rows on each side can produce four

In this invented dataset, order O-17 has lines of 120 and 80, and two payments of 100 each. Joining both tables only on the order ID lets each line match both payments: 2×2 = 4 rows. The joined line total becomes 120 + 120 + 80 + 80 = 400, and the joined payment total becomes 100 + 100 + 100 + 100 = 400. The two totals still agree, yet both have doubled from the source total of 200.

Repair depends on the question. To check whether each order was paid, aggregate each source to one row per order first: O-17 has 200 ordered and 200 paid. To allocate payments to individual lines, obtain a confirmed allocation rule or line-level key instead of copying the order’s total payment onto every line. Difference comparison requires unique keys, while joins expand duplicate matches; deleting legitimate lines to bypass a comparison error is not a repair.

04Follow the inputs through reconciliation and joining

In the data workbench, join and difference operations use the two selected original tables; group summaries use current working data. Adopting a cleaning result does not replace those original tables. Export and reimport a cleaned version when it should become a comparison source, then verify its name, count and fields.

Difference comparison requires unique keys, joining can expand repeated matches, and amount reconciliation aggregates by key. A duplicate-key warning may mean that the wrong operation was selected. Use composite-key deduplication only when evidence warrants removing records; legitimate split payments must remain.

The exercise needs only reconciliation and joining. For other inputs, choose an additional entry only when a parsing, field-definition or delivery problem requires it. These are alternatives for specific issues, rather than nine mandatory processing steps.

1. 1
   Import orders.csv and payments.csv into table reconciliation as left and right. Select Order, Amount and absolute tolerance 0.02. Generate the result and compare each key with the reference table.
2. 2
   To inspect expansion, import the same files into table joining. Select Order and a left join. Expect seven rows: four for O-17, two for O-18 and one for O-20. O-19 is absent from the left join. A full join produces eight.
3. 3
   Filter the four O-17 matches. Each side now displays 400 because values repeat across matches. Source reconciliation must still use the original 200 against 200, rather than summing the expanded display.

• Inspect a CSV table: Check delimiters, quoted line breaks, header position and a known first and last record before transforming.
• Convert CSV to Excel: Confirm that IDs and dates survived import, then reopen the workbook to inspect the actual cells.
• Define a field dictionary: Map aliases to canonical headers, state units and required values, and inspect the validation report before exporting.
• Clean columns by rule: Apply standardization and deduplication to a sample, retaining the selected key and source information for review.
• Remove exact duplicate lines: Use for line-level duplication only when each line is already the intended record; it does not resolve two conflicting records for one ID.
• Join and compare tables: Inspect key uniqueness before choosing a join, then review one-to-many expansions and unmatched keys.
• Reconcile amounts and exceptions: State aggregation keys and numeric tolerance; investigate differences rather than deleting them to force agreement.
• Summarize reviewed data: Group by the intended dimensions, distinguish blanks from zero and compare source and output totals.
• Choose a spreadsheet formula: Select your spreadsheet application, then test the formula on a normal row, a blank, a duplicate key and a boundary value.

05Tolerance handles amount differences, not row-count problems

The reconciliation tool calculates difference as right total minus left total. Its tolerance is an absolute amount in the selected field’s unit. Suppose illustrative order O-18 has left rows of 50 and 20 and one right row of 70.01: the difference is 0.01. With tolerance 0.02, the amount is within tolerance, but the result still reports different row counts. With tolerance 0, it reports an amount difference first.

Accepting two rows against one needs a business explanation: two legitimate lines might have been settled in one payment, or one line might have been imported twice. Return to source rows rather than increasing tolerance until the warning disappears. Likewise, right-only O-19 remains a right-only record. A blank or “pending” amount is invalid numeric input, not evidence of a zero transaction.

06Make the processing record explain every total change

A useful processing record can be short while retaining the decisive changes: 6 rows imported, 1 confirmed duplicate removed, 5 retained; 1 has a missing amount and remains an exception; 4 contribute to the calculation. “5 retained” does not mean “5 reconciled successfully.” Preserve source file, source row and the actual key so excluded and noncalculable records remain traceable.

If totals change, compare the outputs after trimming, type conversion, deduplication and filtering to find the first stage that changed them. Update the calculable total only after explaining why a particular row should be excluded. A tidier table is not justification for deleting exceptions; the exception table is part of the delivery.

07Deliver a reconciliation that explains its exceptions

Deliver four order-level results with investigations for missing receipt O-20 and missing order O-19. Attach the settlement explanation for two lines versus one payment on O-18. Retain sources and settings. Missing amounts should remain invalid numeric input, rather than being replaced with zero to obtain a reassuring status.

CSV permits quoted line breaks, so count parsed records when using other files. Acceptance depends on explainable ownership, amounts and exceptions. Equal grand totals or a tidy screenshot are insufficient.