Removing duplicates in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Removing duplicates with UNIQUE function

You have a list of names in cells A1:A6:

John
Mary
John
Anna
Mary
Tom

Which formula in cell B1 will return the list without duplicates?

A =SORT(A1:A6, TRUE)

B =REMOVE_DUPLICATES(A1:A6)

C =UNIQUE(A1:A6)

D =FILTER(A1:A6, COUNTIF(A1:A6, A1:A6)=1)

💡 Hint

Use the function that extracts unique values from a range.

The UNIQUE function returns only unique values from the range, removing duplicates. REMOVE_DUPLICATES is not a valid function. FILTER with COUNTIF=1 returns only values that appear once, excluding duplicates that appear multiple times. SORT just sorts the list but does not remove duplicates.

❓ Function Choice

intermediate

Choose the correct function to remove duplicates

You want to remove duplicate entries from a list in Google Sheets. Which function should you use?

A DISTINCT()

B REMOVE_DUPLICATES()

C FILTER()

D UNIQUE()

💡 Hint

Think about the function that returns unique values.

UNIQUE() is the correct function to remove duplicates by returning unique values. REMOVE_DUPLICATES() and DISTINCT() do not exist in Google Sheets. FILTER() is used to filter data but does not remove duplicates by itself.

🎯 Scenario

advanced

Removing duplicates but keeping the first occurrence only

You have a list of product IDs in column A with duplicates. You want to create a list in column B that keeps only the first occurrence of each product ID, preserving the original order. Which formula should you use in B1?

A =UNIQUE(A1:A100)

B =SORT(UNIQUE(A1:A100))

C =FILTER(A1:A100, MATCH(A1:A100, A1:A100, 0)=ROW(A1:A100)-ROW(A1)+1)

D =ARRAYFORMULA(IF(COUNTIF(A$1:A1, A1:A100)=1, A1:A100, ""))

💡 Hint

UNIQUE preserves the first occurrence and order by default.

UNIQUE returns the first occurrence of each value in the order they appear. SORT would reorder the list alphabetically or numerically. FILTER with MATCH and ROW is complex and unnecessary here. ARRAYFORMULA with COUNTIF returns blanks for duplicates but leaves gaps.

📊 Formula Result

advanced

Removing duplicates with multiple columns

You have data in columns A and B:

A1:B5
1, Apple
2, Banana
1, Apple
3, Cherry
2, Banana

Which formula in D1 will return unique rows based on both columns?

A =UNIQUE(A1:B5)

B =UNIQUE(A1:A5 & B1:B5)

C =FILTER(A1:B5, COUNTIF(A1:A5, A1:A5)=1)

D =SORT(UNIQUE(A1:B5))

💡 Hint

UNIQUE works on multiple columns to remove duplicate rows.

UNIQUE applied to a range with multiple columns removes duplicate rows considering all columns. Option A concatenates columns but returns a single column of text, not rows. FILTER with COUNTIF only checks column A and does not remove duplicates properly. SORT just sorts the unique rows but does not affect duplicates removal.

❓ data_analysis

expert

Count unique values ignoring blanks

You have a list of values in A1:A10 with some duplicates and blank cells:

Apple
Banana

Apple
Cherry
Banana

Cherry
Durian
Apple

Which formula correctly counts the number of unique non-blank values?

A =COUNTA(UNIQUE(A1:A10))

B =COUNTUNIQUE(FILTER(A1:A10, A1:A10<>""))

C =COUNTUNIQUE(A1:A10)

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

💡 Hint

COUNTUNIQUE counts unique values; FILTER removes blanks.

COUNTUNIQUE counts unique values but includes blanks if present. FILTER removes blank cells before counting unique values. COUNTA counts non-blank cells but does not count unique values correctly when used with UNIQUE. Option B counts unique including blanks, so it is incorrect.

Practice

1. What does the UNIQUE(range) function do in Google Sheets?

easy

A. It highlights duplicate values in the range.

B. It sorts the values in the range alphabetically.

C. It counts how many times each value appears in the range.

D. It returns only the unique values from the given range, removing duplicates.

Solution

1. Step 1: Understand the purpose of UNIQUE()

   The UNIQUE() function is designed to filter out repeated values and return only distinct entries from a range.

2. Step 2: Compare options with UNIQUE() behavior

   'It returns only the unique values from the given range, removing duplicates.' correctly describes that UNIQUE() returns unique values by removing duplicates.

3. Final Answer:

   It returns only the unique values from the given range, removing duplicates. -> Option D

4. Quick Check:

   UNIQUE(range) = unique values [OK]

Hint: Remember: UNIQUE() filters duplicates, not sorting or counting [OK]

Common Mistakes:

• Confusing UNIQUE() with SORT()
• Thinking UNIQUE() counts duplicates
• Assuming UNIQUE() highlights duplicates

2. Which of these is the correct syntax to remove duplicates from the range A1:A10 in Google Sheets?

easy

A. FILTER_DUPLICATES(A1:A10)

B. REMOVE_DUPLICATES(A1:A10)

C. UNIQUE(A1:A10)

D. DELETE_DUPLICATES(A1:A10)

Solution

1. Step 1: Identify the correct function name

   Google Sheets uses UNIQUE() to remove duplicates, not any other function names.

2. Step 2: Check the syntax format

   The correct syntax is UNIQUE(range), so UNIQUE(A1:A10) is valid and works as expected.

3. Final Answer:

   UNIQUE(A1:A10) -> Option C

4. Quick Check:

   Correct syntax = UNIQUE(range) [OK]

Hint: Use UNIQUE() exactly, no other function removes duplicates [OK]

Common Mistakes:

• Using non-existent functions like REMOVE_DUPLICATES()
• Adding extra parentheses or commas incorrectly
• Confusing with FILTER() function

3. Given the data in cells A1:A6 as:
1
2
2
3
1
4
What will be the result of =UNIQUE(A1:A6)?

medium

A. [1, 2, 3, 4]

B. [1, 2, 2, 3, 1, 4]

C. [4, 3, 2, 1]

D. [1, 3, 4]

Solution

1. Step 1: List unique values from the data

   The values are 1, 2, 2, 3, 1, 4. Unique values in order of first appearance are 1, 2, 3, 4.

2. Step 2: Understand UNIQUE() output

   UNIQUE() returns these unique values as a vertical array: 1, 2, 3, 4.

3. Final Answer:

   [1, 2, 3, 4] -> Option A

4. Quick Check:

   UNIQUE removes duplicates, keeps first order [OK]

Hint: UNIQUE() keeps first occurrence, removes repeats [OK]

Common Mistakes:

• Expecting sorted output instead of original order
• Including duplicates in the result
• Confusing UNIQUE() with SORT()

4. You wrote =UNIQUE(A1:A5, TRUE, FALSE, TRUE) but get an error. What is wrong?

medium

A. TRUE should be FALSE to remove duplicates.

B. UNIQUE() takes at most three arguments, extra TRUE causes error.

C. The range A1:A5 is invalid for UNIQUE().

D. UNIQUE() requires a second argument to work.

Solution

1. Step 1: Check UNIQUE() function arguments

   UNIQUE() accepts one required argument (range) and up to two optional boolean arguments ([by_column], [exactly_once]). Adding a fourth TRUE is invalid syntax.

2. Step 2: Understand error cause

   Passing more than three arguments causes a formula parse error in Google Sheets.

3. Final Answer:

   UNIQUE() takes at most three arguments, extra TRUE causes error. -> Option B

4. Quick Check:

   UNIQUE(range[, by_col][, exact]) only up to 3 args [OK]

Hint: UNIQUE() takes at most 3 arguments, no extras [OK]

Common Mistakes:

• Adding extra arguments to UNIQUE()
• Thinking TRUE/FALSE controls duplicate removal
• Using invalid ranges

5. You have a table with columns A (Name) and B (City) from rows 1 to 8. Some rows have duplicate Name and City pairs. Which formula removes duplicate rows considering both columns together?

hard

A. UNIQUE(A1:B8)

B. UNIQUE(A1:A8)

C. UNIQUE(B1:B8)

D. UNIQUE(A1:B8, TRUE)

Solution

1. Step 1: Understand removing duplicates by multiple columns

   To remove duplicates based on both Name and City, you must include both columns in the UNIQUE() range.

2. Step 2: Check options for correct range

   UNIQUE(A1:B8) uses UNIQUE(A1:B8), which considers both columns together and removes duplicate rows correctly.

3. Final Answer:

   UNIQUE(A1:B8) -> Option A

4. Quick Check:

   UNIQUE(multi-column range) removes duplicate rows [OK]

Hint: Include all columns in UNIQUE() to remove full-row duplicates [OK]

Common Mistakes:

• Using only one column range to remove multi-column duplicates
• Adding extra arguments to UNIQUE()
• Expecting UNIQUE() to sort automatically