UNIQUE over two columns: distinct pairs
Give UNIQUE several columns and it removes duplicate rows, not duplicate values.
Task:
Each rep can cover several regions. In E2 list every distinct rep-and-region combination from the visit log, one row each.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of UNIQUE
0/4The same formula in the other shapes it takes at work.
- Pull the distinct regions out of an export
UNIQUE returns each value once, in the order it first appears.
CoreBeginner - UNIQUE, then a count beside each value
Spill the distinct list, then count each item against the source.
Intermediate - UNIQUE with exactly_once: the one-time customers
UNIQUE's third argument keeps only values that appear a single time.
Intermediate - UNIQUE over two columns: distinct pairs
Give UNIQUE several columns and it removes duplicate rows, not duplicate values.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Visit | Rep | Region | |
| 2 | Ana | North | V1 | |||
| 3 | Ben | South | V2 | |||
| 4 | Ana | North | V3 | |||
| 5 | Ana | East | V4 | |||
| 6 | Ben | South | V5 | |||
| 7 | Cy | North | V6 |
What this exercise teachesMay contain the answer
Ana appears three times and North three times, but Ana–North is one combination and Ana–East another. UNIQUE over both columns keeps each pairing once — the shape you need for a coverage map or a lookup key.