List each customer once with UNIQUE
PremiumIn D2, list every customer in B2:B8 once, without the repeats, with UNIQUE.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Customer | Customers | |||
| 2 | 5001 | Northwind | ||||
| 3 | 5002 | Brightside | ||||
| 4 | 5003 | Northwind | ||||
| 5 | 5004 | Oakline | ||||
| 6 | 5005 | Brightside | ||||
| 7 | 5006 | Pinecrest | ||||
| 8 | 5007 | Northwind | ||||
| 9 |
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 keeps the first time each value appears and drops the repeats, in the order they come in the data: Northwind, Brightside, Oakline and Pinecrest. The list spills down from D2 and grows when a new customer is added.
Common mistake: Giving UNIQUE more than one column. It then looks for unique rows, and rows with different order numbers all count as different.
The answer
- D2
=UNIQUE(B2:B8)
=UNIQUE(B2:B8) lists each value of the range once. Unlike Remove Duplicates on the Data tab, the original data stays as it is and the list updates itself.
Wrap the list in COUNTA, =COUNTA(UNIQUE(B2:B8)), or in ROWS.