The practical answer
Order plus line number can identify a detail row when that matches your business rules. Sheet batch compares the selected field values together and keeps the first complete matching key. Rows with a blank key component remain. Our six-row exercise produces four rows with Order alone and five with Order plus Line; conflicting descriptions still need a decision.
Use six rows that make accidental deletion visible
This original exercise contains fictional records. Save the six rows in the displayed order as a CSV with headers Order, Line and Item. Identifiers are text labels, rather than quantities to sum. The third record deliberately adds corrected to its description so that you can observe which version survives.
Microsoft’s duplicate-value guidance recommends reviewing duplicates, preserving original data and choosing the relevant columns. The blank-key and ordering behavior below describes this site’s sheet batch tool specifically. Do not assume that every spreadsheet program handles those cases identically.
| Order | Line | Item |
|---|---|---|
| O-01 | 1 | Red mug |
| O-01 | 2 | Blue mug |
| O-01 | 1 | Red mug corrected |
| O-02 | 1 | Plate |
| (blank) | 1 | Unknown A |
| (blank) | 1 | Unknown B |
Confirm headers and the unit represented by a row
Upload a copy to sheet batch. Check the selected source, header mapping and preview. Generate a merged result without selecting deduplication fields first: input and output should both contain six rows. A missing row at this stage needs an import investigation, rather than a claim that deduplication worked.
For several input files, use the header-mapping checks before defining the key. Confirm that equivalent fields map to the same output column. Recheck deduplication settings after changing sources or mappings. Keep provenance columns so that a retained record can be traced to its source file and row.
Compare an order key with an order-line key
Expand Optional: deduplicate by fields, select only Order, and generate the result. Four rows remain: the first O-01 record, the O-02 record and both blank-order records. Blue mug belonged to a different line of O-01, but the order-only key discarded it. A plausible smaller total therefore hides a real loss of detail.
Add Line to the selected fields and generate again without changing the input order. Five rows remain, with one removed. O-01/2 now survives, while the third record shares O-01/1 with the first. Inspect the retained description: it should still be Red mug. The tool does not interpret corrected as proof that a later row is authoritative.
Same input, different record identity
Order alone: 4 rows; O-01/2 disappears
Order plus Line: 5 rows; O-01/2 remains
| Setting | Rows | Meaning |
|---|---|---|
| No deduplication | 6 | Complete practice input |
| Order | 4 | Different lines of one order collapse |
| Order plus Line | 5 | First O-01/1 and both blank orders remain |
Handle blank keys and conflicting revisions explicitly
If any selected key component is empty or contains only whitespace, this tool retains the row. Unknown A and Unknown B must both remain in the five-row output. They belong in a follow-up review queue; preserving them does not establish that they are unique records.
Nonempty keys compare their original text values without automatic case, spacing or approximate-name normalization. If your business treats O-01 and o-01 as equivalent, define that rule and preserve the original values before cleaning. A requirement to keep the latest revision also needs a trustworthy timestamp and a conflict policy. First-row retention cannot infer either.
Make the unresolved decision visible
Same key, different content
List both versions with provenance and obtain an authoritative decision before preparing the final input.
Partly missing key
Keep a review queue and restore supported identifiers before rerunning the operation.
Similar-looking text
Document normalization rules and check whether cleaning creates new conflicts between previously separate keys.
Reconcile the exported five rows
Export XLSX and open the new file. Check for O-01/1, O-01/2, O-02/1, Unknown A and Unknown B, with Red mug retained on the first line. Provenance should point to that original row, rather than the removed corrected version. A counter saying one row was removed is insufficient evidence on its own.
This workflow organizes values; do not assume it preserves the original workbook’s formulas, styling or complex layout. If you next need separate files by department or order, follow the grouped export and manifest checks as a separate step and reconcile their row totals.
Acceptance for this exercise
- Keep the original six-row copy without overwriting it.
- Select Order and Line and regenerate a five-row result.
- Confirm Blue mug and both blank orders remain, with the first O-01/1 retained.
- Match the actual XLSX and provenance to the preview, and record the description conflict separately.
Reproduce the complete store-report workflow
The companion store sample exercises the complete reporting workflow and is separate from the duplicate-record example above. Its eight records have distinct IDs and all eight should remain. A repeated store name is not a duplicate transaction. Convert the amount field, trim store names, and compare identifiers and source rows before accepting changes.
Keep all eight original identifiers after cleaning. Summing October 1–7 and October 8–14, 2026 yields fictional totals of 3,000 and 3,600 (+20%). Matching totals cannot replace a record check: deleting one record and adding another of equal value can preserve the sum. Compare the identifier set and provenance as well.
Give each removed row a recorded reason
The separate order pack preserves the six-row sequence above and includes expected CSVs for both rules plus a decision log. Keep the unfiltered result as your baseline, then run Order-only and Order+Line independently. Do not experiment on an already reduced output. Answer files are for comparison and must not be merged with orders.csv.
The third record for O-01/1 says corrected, but provides no timestamp or authoritative status. Five rows is the answer for the first-kept complete-key algorithm, not proof that the right business version was chosen. Retaining the later row in real work requires version evidence, an explicit priority rule and record review. Casually swapping file order hides that decision.
| Source line | Order+Line decision | First-kept output | Unresolved business decision |
|---|---|---|---|
| 2: O-01 / 1 / Red mug | First complete key | Keep | Compare version evidence with line 4 |
| 3: O-01 / 2 / Blue mug | A different product line | Keep | Repeated Order alone does not justify deletion |
| 4: O-01 / 1 / corrected | Same complete key, changed text | Remove | Establish whether corrected is authoritative |
| 5: O-02 / 1 / Plate | Another complete key | Keep | Ordinary record |
| 6–7: blank Order / 1 | Incomplete keys | Keep both | Resolve identifiers or quarantine separately |
Separate duplicate keys, identical rows and newer versions
Order+Line asks whether an order-line key has appeared before. Comparing every business column asks whether all content matches. Selecting the latest version needs time, version or status rules. These questions can yield different answers: the two O-01/1 rows have different Item text, so whole-row deduplication retains both; composite-key deduplication retains the first; neither establishes the latest without evidence.
Record six input rows, five retained rows, one rule-based removal, two blank Order values still unresolved and one same-key text conflict. Avoid claiming every duplicate and error is resolved. Continue with the identifier and text-type checks so the business key remains intact across import and export.
A sample you can reproduce
Two fictional store CSVs, eight records. After aligning headers and cleaning amounts: Oct 1–7 = 3,000; Oct 8–14 = 3,600; change = +20%. No records need deduplication in this sample.
The ZIP includes the field mapping, date ranges and expected totals. Files are synthetic; no account is needed.
Common questions
Is a key with more columns always better?
No. Adding a mutable description can prevent two versions of one business record from matching. Select fields that define identity, rather than adjusting columns to produce a convenient removal count.
Does a later row replace an earlier row?
No. This tool retains the first complete matching key. Keeping the latest version requires a supported timestamp and an explicit business rule; a filename or the word corrected is not sufficient.
Why were the blank orders retained?
Rows with any blank selected key component remain to avoid collapsing unknown identities. Review and complete those identifiers separately. Retention does not prove the records are unique.
Further reading & sources
Put the guide into practice
Open the relevant tool, preview the result and check your exported file.
