Data Validation & Cleaning
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.
Why plain COUNTIF($A$2:$A$6, A2) = 1 lets padded duplicates slip through
The classic Data Validation formula =COUNTIF($A$2:$A$6, A2) = 1 catches case-variants such as "inv-101" vs. "INV-101", but fails as soon as a user pastes a trailing or leading space (" INV-101 "), treating the padded string as brand new while breaking downstream lookups.
Generating a canonical key column B2 = UPPER(TRIM(A2)) and validating =COUNTIF($B$2:$B$6, B2) = 1 rejects exact, case-shifted, and space-padded duplicates simultaneously.
| Entered Key | Canonical UPPER(TRIM) | Plain COUNTIF = 1 | Normalized = 1 |
|---|---|---|---|
| "INV-101" | "INV-101" | TRUE (1) | FALSE (2 - Collision!) |
| "INV-102" | "INV-102" | TRUE (1) | TRUE (1 - Unique) |
| " inv-101 " (padded) | "INV-101" | TRUE (1 - Missed!) | FALSE (2 - Caught!) |
Canonical Key & Uniqueness Validation Formula
Normalize spacing and case in column B with UPPER(TRIM(A2)), then enforce COUNTIF($B$2:$B$6, B2) = 1 in your validation rule.
=COUNTIF($B$2:$B$6, B2) = 1Applying Validation Rules via the Arte Sheets AI Assistant
In Arte Sheets, editors can apply Data Validation rules manually or prompt the AI Assistant to format identifier columns and attach validation constraints across selected ranges, catching duplicate invoice or SKU entries immediately.
=COUNTIF(D2:D6, FALSE)Try the example
Use the original values or change them to test your understanding.