Remove repeated product rows for financial reconciliation

To remove repeated product rows for financial reconciliation, first check whether they represent the same transaction line or separate sales of the same product. Deleting every repeated SKU can remove legitimate revenue, refunds, or fees.

Work from a copy of the CSV, compare suspected duplicates with the source records, and verify how the changes affect the totals. The steps below help you clean the file while keeping a record of what changed.

Remove confirmed duplicate rows

1. Open a copy of the export. Open the CSV Editor and load your product CSV. Keep the original file unchanged so you can compare it with the cleaned version or start again if needed.
2. Check the columns. Find the transaction or order ID, line ID, SKU, quantity, amount, currency, and date. Also look for a status or transaction type that distinguishes sales from refunds. If the file lacks a reliable way to identify individual lines, get a more detailed export before deleting anything.
3. Find possible repeats. Sort by a transaction or order ID, then inspect rows with the same ID and line reference. Search for an ID if you need to find all of its appearances. Make a note of the rows that appear to repeat, rather than assuming that matching products are duplicates.
4. Compare them with the source. Check the original order, invoice, settlement report, or accounting record. Confirm which lines should exist and whether an apparent repeat is a valid separate charge, refund, adjustment, or export error. Record the relevant IDs and amounts for the rows you plan to remove.
5. Remove only confirmed extras. Use the editor's duplicate removal only if you can verify that its matching rule will remove the confirmed extras without affecting valid rows. If you cannot verify that, or legitimate rows are identical, do not use automatic removal. Obtain a corrected export or remove the confirmed extras individually in an editor that allows it.
6. Save and check the result. Save the cleaned file under a new name. Reopen it and compare its rows and amounts with your notes and the source records. Keep the original, the cleaned file, and your change record together for the reconciliation.

The CSV Editor processes the file in your browser. Your file stays on your device and is not uploaded.

Decide which rows are actually duplicates

A SKU identifies a product, not a sale. The same SKU can appear in many orders, and it can even appear on more than one line of the same order. A transaction ID and line ID together are often a better starting point, but their meaning depends on how the source system creates the export.

For example, suppose an export contains these product line amounts before shipping or tax:

• Order A101, line 1: SKU-7, quantity 2, amount $25.00.
• Order A101, line 1: SKU-7, quantity 2, amount $25.00.
• Order A101, line 2: SKU-7, quantity 1, amount $12.50.

If the source order confirms that line 1 exists only once, the second copy is an extra. Line 2 stays, even though its product matches. The product total changes from $62.50 to $37.50, a difference of $25.00. If the source confirms that both copies of line 1 are legitimate, keep both and investigate why they have the same identifiers.

Rows that look similar can also reflect a partial refund or a correction. Check their signs, dates, statuses, and reference numbers before deciding. An original charge and its reversal may both be needed to explain the net amount.

Check the financial totals after cleaning

Before making changes, record the original row count and the totals you use for reconciliation. Afterward, compare them with the cleaned file. The drop in row count should equal the number of rows you intentionally removed.

The change in each total should also match the amounts on those rows. Keep currencies separate, and use the same definition of amount on both sides: for example, do not compare product subtotals with a settlement total that includes tax, shipping, fees, or refunds. A removed negative amount will increase the resulting net total rather than decrease it.

If the totals still differ, do not remove more rows just to make them match. Check the reporting dates, transaction statuses, fees, and refunds in the source reports. A reconciliation difference can have more than one cause.

Keep a useful record of the changes

For each confirmed removal, note the transaction and line IDs, SKU, amount and currency, the reason for removal, and the source record you checked. Record who made the change and when. If several identical rows have no unique identifiers, note how many copies you retained and removed.

Also check that important values survived the save. Product codes and identifiers can contain leading zeros, while dates and amounts may be interpreted differently by another program. Open the cleaned CSV in the system that will use it and inspect a few known records before relying on the import.