Data Validation & Cleaning
Combine Duplicate Rows & Sum Values Non-Destructively (UNIQUE, COUNTIF & SUMIFS)
Consolidate repeated SKU or customer line items into one summary row per key while preserving raw transaction audit trails and reconciling grand totals.
Why Remove Duplicates destroys revenue when rows represent separate orders
Running a destructive "Remove Duplicates" command on a transaction log deletes repeat SKU rows along with their quantities and revenue! Instead, dynamic consolidation extracts each distinct key once with UNIQUE(A2:A7) and aggregates line counts, units, and revenue via COUNTIF and SUMIFS.
Adding a one-cell control tie SUM(Consolidated_Rev) = SUM(Raw_Rev) proves that zero transaction lines were dropped or double-counted.
| Consolidated SKU | Order Lines (COUNTIF) | Total Units (SUMIFS) | Total Revenue (SUMIFS) |
|---|---|---|---|
| SKU-101 | 3 | 75 | $1,500 |
| SKU-102 | 2 | 60 | $1,800 |
| SKU-103 | 1 | 10 | $500 |
| Reconciliation | 6 lines | 145 units | $3,800 (Matches raw log) |
Distinct Key Extraction & Multi-Column SUMIFS Rollup
Extract distinct keys with UNIQUE and roll up each numeric measure against the consolidated key.
=SUMIFS($C$2:$C$7, $A$2:$A$7, E2)Consolidating Attached CSV/XLSX Exports in Arte Sheets
When reconciling external exports in Arte Sheets, you can attach .csv, .tsv, or .xlsx files to the AI Assistant to inspect structured excerpts and build non-destructive UNIQUE + SUMIFS consolidation tabs alongside the raw import sheet.
=SUM(H2:H4) = SUM(C2:C7)Try the example
Use the original values or change them to test your understanding.