Dynamic arrays › UNIQUE98 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeUNIQUE

UNIQUE: list of delivery cities

Nadia sells hand-drawn batik online from Pekalongan. Before renewing her courier contract, she wants to know which cities her parcels went to this month, without any city repeated.

Task

In E2, write one UNIQUE formula that lists every city in C2:C11 only once, in the order each first appears. The result spills into E2:E7.

Cells to fillE2:E7

Required functionsUNIQUE

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
UNIQUE takes a range and drops repeated values. The first occurrence of each value is the one that stays.
Hint 2
Use the city column without its header, C2:C11. If C1 is included, the word "City" shows up in the list.
Hint 3
Write the formula in E2 only and press Enter. Do not copy it down: the result spreads on its own.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

E2

=UNIQUE(C2:C11)

Explanation

=UNIQUE(C2:C11) returns 6 cities from 10 orders, in order of first appearance: Bandung, Surabaya, Medan and so on. For alphabetical order, wrap it in SORT: =SORT(UNIQUE(C2:C11)). To count the cities, use =COUNTA(UNIQUE(C2:C11)). Watch the third argument: UNIQUE(…,,TRUE) returns only the values that appear exactly once.

Starting worksheet: Sheet1
Row	A	B	C	E
1	Order no.	Customer	City	Destination city
2	INV-0301	Rahmat	Bandung
3	INV-0302	Melati	Surabaya
4	INV-0303	Yusuf	Bandung
5	INV-0304	Clara	Medan
6	INV-0305	Wayan	Yogyakarta
7	INV-0306	Siti	Surabaya
8	INV-0307	Andre	Makassar
9	INV-0308	Putri	Bandung
10	INV-0309	Hendro	Medan
11	INV-0310	Kadek	Denpasar
12
13
14

Fill in the requested cell, then press Check answer.

Validation result