Dynamic Arrays
Extract unique multi-column rows and non-adjacent column pairs (UNIQUE & CHOOSECOLS)
Deduplicate entire multi-column records or specific non-adjacent columns (like Region + Plan) without modifying source data.
Row-level deduplication vs non-adjacent column combinations
UNIQUE(A2:C5) compares all three included values per row. US/Sales/Pro and US/Ops/Pro are distinct, even though they share a region and tier.
UNIQUE(CHOOSECOLS(A2:D5,1,4)) compares only Region and Status. Both US rows above therefore become one US/Active pair.
This creates a separate result without deleting source records. Only the selected columns define uniqueness; other fields are deliberately ignored. The example uses nonblank canonical text labels. Clean inconsistent spacing and types before deduplication when your business rules require it.
| Region (A) | Dept (B) | Tier (C) | Status (D) | Deduplication Result |
|---|---|---|---|---|
| US | Sales | Pro | Active | Kept (#1 in both) |
| US | Sales | Pro | Active | Exact row duplicate -> Removed! |
| US | Ops | Pro | Active | Distinct in A:C; same Region/Status pair |
| EU | Sales | Enterprise | Trial | Kept |
Count or spill distinct multi-column records vs non-adjacent column pairs
UNIQUE(A2:C5) keeps US|Ops|Pro separate from US|Sales|Pro, whereas UNIQUE(CHOOSECOLS(A2:D5, 1, 4)) collapses both into a single US|Active pair.
=ROWS(UNIQUE(A2:C5))Deduplicate non-adjacent columns (Column 1 and Column 4)
CHOOSECOLS picks arbitrary column numbers in any order before UNIQUE removes duplicate combinations.
=UNIQUE(CHOOSECOLS(A2:D5,1,4))Try the example
Use the original values or change them to test your understanding.