Dynamic Arrays
Extract values that appear ONLY ONCE (UNIQUE 3rd argument: distinct vs singleton)
List identifiers that occur exactly once in a supplied range, rather than returning every distinct identifier.
Distinct (1+ occurrences) vs Singleton "Exactly Once" (3rd argument = TRUE)
UNIQUE(range) returns one copy of every distinct value. UNIQUE(range,FALSE,TRUE) returns only values that occur exactly once in the range.
No singletons does not mean all records are paired: a key appearing three or five times is also excluded. Reconciliation requires additional checks such as amounts, signs and matched counterparties. One appearance in this extract also does not establish a customer’s full purchase history.
Use nonblank normalized identifiers. The IFERROR fallback can hide source errors as well as the empty-array condition, so inspect errors separately.
| Ledger Ref (A2:A7) | Occurrences | Standard UNIQUE(A2:A7) | Singleton UNIQUE(A2:A7, FALSE, TRUE) |
|---|---|---|---|
| INV-101 (appears 2×) | 2 (Paired) | Included once | Excluded completely! |
| INV-102 (appears 2×) | 2 (Paired) | Included once | Excluded completely! |
| INV-103 (appears 1×) | 1 (Orphan!) | Included | Included! |
| INV-104 (appears 1×) | 1 (Orphan!) | Included (4 total) | Included! (2 orphans) |
Extract and list items that appear exactly once
Joins the singleton identifiers into text, with a fallback when none can be returned.
=IFERROR(TEXTJOIN(", ", TRUE, UNIQUE(A2:A7, FALSE, TRUE)), "No singletons")Compare standard distinct count vs singleton orphan count
Standard UNIQUE returns 4 distinct IDs here, whereas exactly_once = TRUE isolates just the 2 unpaired orphans.
=ROWS(UNIQUE(A2:A7))Try the example
Use the original values or change them to test your understanding.