Array Functions
Beginner
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.
Learning Objectives:
- Use UNIQUE to deduplicate a column
- Recognise a spilled result
- Understand that UNIQUE preserves order
Hints
Solve without hints for +5 XP
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 |
What this exercise teaches (contains 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.