UNIQUE with exactly_once: the one-time customers
UNIQUE's third argument keeps only values that appear a single time.
Task:
Marketing wants to win back customers who ordered only once. In D2 list the customers who appear exactly once in the order log.
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 | |
|---|---|---|---|---|
| 1 | Order | Customer | Ordered once | |
| 2 | 1001 | Maple Cafe | ||
| 3 | 1002 | Oak & Co | ||
| 4 | 1003 | Maple Cafe | ||
| 5 | 1004 | Pine Studio | ||
| 6 | 1005 | Rowan Ltd | ||
| 7 | 1006 | Oak & Co | ||
| 8 | 1007 | Maple Cafe |
What this exercise teachesMay contain the answer
Plain UNIQUE answers "who are our customers?"; with exactly_once it answers "who came once and never returned?" — a different and often more useful list. Maple Cafe and Oak & Co are repeat customers and drop out.