UNIQUE function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of UNIQUE with a simple list

Given the list in cells A1:A6:

A1: Apple
A2: Banana
A3: Apple
A4: Orange
A5: Banana
A6: Grape

What is the output of the formula =UNIQUE(A1:A6) when entered in cell B1?

Google Sheets

=UNIQUE(A1:A6)

A Apple, Banana, Orange

B Apple, Banana, Apple, Orange, Banana, Grape

C Apple, Banana, Orange, Grape

D Banana, Apple, Orange, Grape

💡 Hint

UNIQUE removes repeated values and keeps the first occurrence order.

The UNIQUE function returns only the distinct values from the list, preserving the order of their first appearance. So duplicates like the second 'Apple' and 'Banana' are removed.

📊 Formula Result

intermediate

UNIQUE with multiple columns

Given the data in cells A1:B5:

A1: Name, B1: City
A2: John, B2: NY
A3: Anna, B3: LA
A4: John, B4: NY
A5: Mike, B5: SF

What will be the output of =UNIQUE(A2:B5) starting at cell D1?

Google Sheets

=UNIQUE(A2:B5)

A NY<br>LA<br>SF

B John NY<br>Anna LA<br>Mike SF

C John<br>Anna<br>Mike

D John NY<br>Anna LA<br>John NY<br>Mike SF

💡 Hint

UNIQUE treats each row as a whole when given multiple columns.

When UNIQUE is applied to multiple columns, it returns unique rows. The duplicate row 'John NY' appears twice but only the first is kept.

❓ Function Choice

advanced

Choosing the right function to get unique values ignoring blanks

You have a list in column A with some blank cells. You want to get unique values from this list but exclude any blank cells from the result.

Which formula will correctly return unique non-blank values?

A =UNIQUE(FILTER(A1:A10, A1:A10<>""))

B =UNIQUE(A1:A10)

C =FILTER(UNIQUE(A1:A10), UNIQUE(A1:A10)<>"")

D =SORT(UNIQUE(A1:A10))

💡 Hint

Filtering out blanks before applying UNIQUE is the key.

Option A filters out blank cells first, then applies UNIQUE, so blanks are excluded. Option A includes blanks. Option A tries to filter after UNIQUE but UNIQUE returns blanks as well, so FILTER won't remove them properly. Option A sorts but does not remove blanks.

🎯 Scenario

advanced

Using UNIQUE with case sensitivity

You have a list in column A:
A1: apple
A2: Apple
A3: APPLE
A4: banana
A5: Banana

You want to get unique values treating different cases as different entries.

Which approach will achieve this in Google Sheets?

A Use =UNIQUE(A1:A5 & CHAR(ROW(A1:A5)))

B Use =UNIQUE(ARRAYFORMULA(EXACT(A1:A5, A1:A5)))

C Use =UNIQUE(A1:A5, FALSE)

D Use =UNIQUE(A1:A5) directly

💡 Hint

UNIQUE is case-insensitive by default; trick it by making entries unique with row numbers.

UNIQUE is case-insensitive, so 'apple', 'Apple', 'APPLE' are treated the same. Option A appends the row number to each entry, making them unique strings, so UNIQUE returns all. Options A and C treat cases as same. Option A is invalid syntax and logic.

❓ data_analysis

expert

Counting unique values with conditions

You have a table with sales data:

Column A (Product): A2:A10
Column B (Region): B2:B10

You want to count how many unique products were sold in the "East" region.

Which formula will correctly give this count?

A =COUNTA(UNIQUE(FILTER(A2:A10, B2:B10="East")))

B =SUM(UNIQUE(FILTER(A2:A10, B2:B10="East")))

C =COUNT(UNIQUE(FILTER(A2:A10, B2:B10="East")))

D =COUNTUNIQUE(FILTER(A2:A10, B2:B10="East"))

💡 Hint

COUNTUNIQUE counts unique values directly.

Option D uses COUNTUNIQUE with FILTER to count unique products in the East region correctly. Option D counts all unique values but COUNTA counts text correctly, so it seems valid but COUNTUNIQUE is more direct. Option D uses COUNT which counts numbers only, so returns 0. Option D sums text values causing error.