An order ID is not always a unique row key
In the 14-record sales sample, order 000101 has two different line IDs. One line contains two notebooks and the other contains three pens. Deleting every repeated order ID would remove a legitimate item from the report.
Work with the example
Original export ↓Prepared export ↓All records are fictional. Import CSV identifier columns as Text. Quoted CSV fields do not define spreadsheet column types.
| Order / line | Value after discount | Decision |
|---|---|---|
| 000101 / 1 | RM25.00 | Keep the notebook line. |
| 000101 / 2 | RM9.60 | Keep the separate pen line. |
| 000103 / 1, first occurrence | RM48.00 | Keep. |
| 000103 / 1, exact repeat | RM48.00 | Remove the known repeated record. |
Confirm the repetition across all fields
Source data records 4 and 5 are identical across every column. In this fictional exercise, record 5 is deliberately inserted as an accidental repeated export record. That stated provenance supports removing one copy. A real file requires its own evidence: a repeated row might be an event record or a legitimate recurring charge.
A composite order-plus-line key is a useful candidate for a line-item table. It is not proof that two records are identical. If the same composite key has different quantities, dates, or statuses, flag the conflict rather than discarding the later record. Keep export timestamps if the source uses them to indicate updates.
Measure the effect before removing anything
The exact repeated line contributes RM48.00. With all other reporting rules unchanged, counting it twice increases the weekly net amount from RM267.60 to RM315.60. Removing the second line of order 000101 instead would understate the valid report by RM9.60.
Record counts help explain the transformation: 14 original records become 13 prepared lines. Only nine of those lines qualify for the reporting week and Shipped status. Those nine lines represent eight distinct orders. None of those counts should be described simply as “nine sales” without defining the measure.
Preserve an audit trail
Retain the original file and record which source record was removed, the comparison fields, and the reason. In the workbook, Orders retains a Source record column, while Raw export preserves the original record sequence. Source-record numbers are specific to that file and must not be reused as stable IDs across refreshed exports.
Use the comparison tool to find repeated-key groups in two snapshots. It intentionally leaves those groups unresolved. For a real cleanup, review every row in a repeated group before making a removal decision, then reconcile the resulting count and amount.
Sources & further reading
The sample data and worked calculations are original instructional examples. Spreadsheet menus and supported features depend on your installed version. See the verification method for the checks performed.
Continue the workflow