A sorted list without duplicates
PremiumIn D2, list every rep in B2:B9 once, sorted from A to Z. Put UNIQUE inside SORT.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Rep | Reps A to Z | |||
| 2 | Mon | Lena | ||||
| 3 | Mon | Ahmed | ||||
| 4 | Tue | Lena | ||||
| 5 | Tue | Chris | ||||
| 6 | Wed | Ahmed | ||||
| 7 | Wed | Bea | ||||
| 8 | Thu | Chris | ||||
| 9 | Fri | Lena | ||||
| 10 |
Premium lesson
Unlock SORT and UNIQUE and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:TRIM, PROPER and UPPERLEFT, RIGHT and MID
Need help?
Why this works
UNIQUE(B2:B9) gives Lena, Ahmed, Chris and Bea, in the order they first appear. SORT puts that list in alphabetical order: Ahmed, Bea, Chris, Lena. One formula makes a clean drop-down source or the row labels of a summary table.
Common mistake: Using SORT or UNIQUE alone. SORT keeps the repeats, and UNIQUE keeps the order of the data.
The answer
- D2
=SORT(UNIQUE(B2:B9))
=SORT(UNIQUE(B2:B9)). UNIQUE removes the repeats and SORT puts what is left in order.