Logical
Check if a range contains duplicates (single-cell audit gate)
Compare total non-blank entries against distinct UNIQUE values to trigger a sheet-level duplicate warning and count excess repeat rows.
Sheet-level duplicate gate before database import
Row-level COUNTIF flags work well on short lists, but on a 5,000-row SKU import you also need a single top-of-sheet banner cell that confirms zero duplicates exist anywhere below.
Filter out empty cells first—vals = FILTER(A2:A6, (ISNUMBER(A2:A6) + (ISTEXT(A2:A6) * (A2:A6 <> ""))) > 0)—then compare ROWS(vals) against ROWS(UNIQUE(vals)). Whenever ROWS(vals) > ROWS(UNIQUE(vals)), at least one duplicate exists.
| SerialID (A) | Non-Blank Rows | Distinct UNIQUE Rows | Excess Duplicates |
|---|---|---|---|
| SN-101 | 4 total | 3 distinct | 1 duplicate ("SN-102") |
| SN-102 | |||
| (blank) | |||
| SN-102 | |||
| SN-103 |
Single-cell duplicate check and excess duplicate count
Using ISNUMBER + ISTEXT*(<> "") keeps numeric 0 IDs while skipping empty cells cleanly.
=IF(SUMPRODUCT(--((ISNUMBER(A2:A6) + (ISTEXT(A2:A6) * (A2:A6 <> ""))) > 0))=0, FALSE, LET(v, FILTER(A2:A6, (ISNUMBER(A2:A6) + (ISTEXT(A2:A6) * (A2:A6 <> ""))) > 0), ROWS(v) > ROWS(UNIQUE(v))))Count how many excess duplicate entries exist
Subtracting distinct count from total populated count returns 0 when every ID is unique.
=IF(SUMPRODUCT(--((ISNUMBER(A2:A6) + (ISTEXT(A2:A6) * (A2:A6 <> ""))) > 0))=0, 0, LET(v, FILTER(A2:A6, (ISNUMBER(A2:A6) + (ISTEXT(A2:A6) * (A2:A6 <> ""))) > 0), ROWS(v) - ROWS(UNIQUE(v))))Inputs and interpretation
The predicate includes numbers and nonempty text, but excludes booleans. Spaces count as text and errors need cleaning first. Choose case, whitespace, and numeric-text normalization rules that match the destination database; this is not proof of uniqueness under every database collation.
Try the example
Use the original values or change them to test your understanding.