Pull the distinct regions out of an export
UNIQUE returns each value once, in the order it first appears.
Task:
Sales pulled this report out of the CRM and every order repeats its region. You need the list of regions, each once, to build a summary against. In E2, use UNIQUE to list the distinct regions from B2:B9.
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 | |
|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Regions | |
| 2 | SO-1041 | North | 1200 | ||
| 3 | SO-1042 | South | 890 | ||
| 4 | SO-1043 | North | 2100 | ||
| 5 | SO-1044 | East | 640 | ||
| 6 | SO-1045 | South | 1750 | ||
| 7 | SO-1046 | North | 430 | ||
| 8 | SO-1047 | West | 980 | ||
| 9 | SO-1048 | East | 1310 |
Functions used here
What this exercise teachesMay contain the answer
One formula replaces copy, paste-special, Remove Duplicates, and doing it all again next month when the export changes. Because it spills, the list resizes itself when a new region appears.