Removing duplicates in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula removing duplicates?

Given the list in cells A1:A6: {"Apple", "Banana", "Apple", "Orange", "Banana", "Grape"}, what will be the output of the formula =UNIQUE(A1:A6) when entered in cell B1?

A ["Apple", "Banana", "Orange", "Grape"]

B ["Apple", "Banana", "Apple", "Orange", "Banana", "Grape"]

C ["Apple", "Banana", "Orange"]

D ["Apple", "Orange", "Grape"]

💡 Hint

The UNIQUE function returns only the first occurrence of each item, removing duplicates.

The UNIQUE function scans the range and returns only unique values in the order they first appear. So duplicates like the second "Apple" and second "Banana" are removed.

❓ Function Choice

intermediate

Which function removes duplicates from a list?

You have a list of names in column A. Which Excel function will create a new list without any repeated names?

A COUNTIF

B FILTER

C SORT

D UNIQUE

💡 Hint

Think about the function that extracts only distinct values.

The UNIQUE function returns a list of distinct values from a range, effectively removing duplicates.

🎯 Scenario

advanced

How to remove duplicates but keep the last occurrence?

You have a list in A1:A7: {"Cat", "Dog", "Cat", "Bird", "Dog", "Fish", "Cat"}. You want to create a list without duplicates but keeping the last occurrence of each item. Which formula in B1 will do this?

A =SORT(UNIQUE(A1:A7, FALSE, TRUE))

B =INDEX(A1:A7, SORT(MATCH(UNIQUE(A1:A7), A1:A7, 0),, -1))

C =SORT(UNIQUE(A1:A7),, -1)

D =SORTBY(UNIQUE(A1:A7), SEQUENCE(COUNTA(A1:A7)), -1)

💡 Hint

UNIQUE has an option to keep last occurrences instead of first.

The UNIQUE function's third argument, when TRUE, keeps the last occurrence of each value. So =UNIQUE(A1:A7, FALSE, TRUE) returns unique values keeping last occurrences.

📊 Formula Result

advanced

What error occurs with this formula?

What error will this formula produce? =UNIQUE(A1:A5, TRUE, TRUE) if A1:A5 contains text values?

A #NAME? error

B #VALUE! error

C Returns unique values keeping last occurrences

D Returns unique values sorted ascending

💡 Hint

Check the meaning of the second argument in UNIQUE.

The second argument in UNIQUE is 'by_col' which expects TRUE or FALSE. Setting it TRUE means to check columns, but if the range is a single column with text, it causes a #VALUE! error.

❓ data_analysis

expert

How many unique values after removing duplicates?

Given this list in A1:A10: {"Red", "Blue", "Red", "Green", "Blue", "Yellow", "Green", "Red", "Yellow", "Black"}, how many unique values will the formula =COUNTA(UNIQUE(A1:A10)) return?

A 6

B 7

C 5

D 4

💡 Hint

Count distinct colors ignoring duplicates.

The unique colors are Red, Blue, Green, Yellow, Black, so 5 unique values. But counting carefully: Red, Blue, Green, Yellow, Black = 5 unique values. The correct count is 5, so option C is correct.