Imported CSV Type Coercion, Composite Keys & Deduplication Audits

Who this is for

Stage 4: Data Cleaning, Text Parsing & Hygiene Audits — Designed for learners mastering value, currency stripping & trailing minus, text formatting, textjoin & pipe composite keys, expanding countif/countifs deduplication & locale repair, structural checklists, unique+filter & paste values through interactive spreadsheet examples.

What you will practise

• Coerce the text-formatted invoice strings in A2:A5 into real numbers in B2:B5 using VALUE(A2), then compute the true batch total in B6 with SUM.
• Strip "$" and "," from A2:A5 and coerce to numbers in B2:B5 using VALUE + nested SUBSTITUTE, then sum the cleaned payouts in B6.
• Parse ledger postings in A2:A5 (including trailing-minus credits) into signed numbers in B2:B5, then compute net ledger balance in B6.
• Assemble ISO archive keys A2 & "_" & TEXT(B2, "yyyy-mm-dd") in D2:D5 and formatted statement badges "Amt: $" & TEXT(C2, "#,##0.00") in E2:E5.
• Build clean comma-delimited shipping labels in E2:E5 skipping blank Suite cells via TEXTJOIN(", ", TRUE, A2:D2), and build compact routing tags in F2:F5 via CONCAT(D2, "-", UPPER(C2)).
• Generate canonical composite keys in D2:D5 from Vendor (A2:A5) and Invoice (B2:B5), then reconcile bank remittance keys F2:F3 against $D$2:$D$5 in G2:G3 using XLOOKUP.
• Compute running occurrence numbers in B2:B6 with expanding COUNTIF($A$2:A2, A2), then label row 1st hits "Keep Primary" and repeats "Drop Duplicate" in C2:C6.
• Count running (Order ID, SKU) occurrences in D2:D6 with expanding COUNTIFS, then sum deduplicated net order revenue in D7 with SUMIFS(C2:C6, D2:D6, 1).

Your learning path

1. 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.

2. SUMIFS & TEXTJOIN+FILTER vs XLOOKUP When Duplicate Keys Exist

   Choose deliberately between one matching row, the sum of every matching amount, and a joined list of descriptions. Test literal invoice IDs, missing keys and zero totals.

3. Get First & Last Text Value in a Mixed Range (Wildcard XLOOKUP)

   Extract the first or last non-empty text entry from a column or row mixed with numbers and blanks using XLOOKUP("?*", range, range, "No Text Found", 2, 1) and reverse search_mode -1.

4. Count, sum and find the maximum in a selected column with CHOOSECOLS

   Select a metric by its unique header, then use SUMPRODUCT to count qualifying rows or total a region and MAX for all regions.

5. Filter and Sum Records from the Last N Weeks (FILTER & SUMIFS Rolling Window)

   Extract and total transactions falling within a rolling N-week lookback window anchored to a report cutoff date, excluding future-dated entries beyond the cutoff.

6. Find highest & lowest value on a specific day of week (WEEKDAY, MAXIFS & MINIFS)

   Use a Monday-first weekday helper to find maximum and minimum numeric readings, while distinguishing no matching dates from a genuine zero result.

7. Minimum value if unique: find the lowest non-tied bid and winning bidder

   Disqualify tied low values and extract the smallest strictly unique number (and its owner) using COUNTIF, FILTER, MIN, and XLOOKUP.

8. Prevent Duplicate Entries (Normalized TRIM + UPPER Uniqueness Rule vs. Plain COUNTIF)

   Block both exact and whitespace-padded stealth duplicates ("INV-101" vs. " inv-101 ") by pairing UPPER(TRIM(A2)) canonical keys with COUNTIF uniqueness rules.