Removing duplicates in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales assistant at a retail company.

📋 Request: Your manager wants a clean list of unique customer emails from recent orders to send a promotion.

📊 Data: You have a list of recent orders with customer names, emails, and purchase dates. Some customers appear multiple times because they ordered more than once.

🎯 Deliverable: Create a new list showing only unique customer emails without duplicates.

Sample Data

Order ID	Customer Name	Email	Purchase Date
1001	Alice Smith	[e-mail]	2024-05-01
1002	Bob Jones	[e-mail]	2024-05-02
1003	Alice Smith	[e-mail]	2024-05-03
1004	Carol Lee	[e-mail]	2024-05-04
1005	David Kim	[e-mail]	2024-05-05
1006	Bob Jones	[e-mail]	2024-05-06
1007	Eva Green	[e-mail]	2024-05-07
1008	Frank Hall	[e-mail]	2024-05-08

1

Step 1: Select a new empty cell where you want the unique email list to start, for example, cell F2.

Expected Result

Cell F2 is selected and ready for formula input.

2

Step 2: Enter the UNIQUE formula to extract unique emails from the Email column (column C).

=UNIQUE(C2:C9)

Expected Result

The formula lists unique emails: [e-mail], [e-mail], [e-mail], [e-mail], [e-mail], [e-mail].

3

Step 3: Press Enter to apply the formula and see the unique emails fill down starting from cell F2.

Expected Result

Cells F2 to F7 show the unique email addresses without duplicates.

4

Step 4: Optionally, add a header 'Unique Emails' in cell F1 for clarity.

Expected Result

Cell F1 displays the header 'Unique Emails' above the list.

Final Result

Unique Emails
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]

✓The UNIQUE formula quickly removes duplicate emails.

✓This clean list can be used for sending promotions without repeats.

Bonus Challenge

Create a unique list of customer names and emails together, so each pair appears only once.

Show Hint

Use UNIQUE with an array range like UNIQUE(B2:C9) to get unique rows of names and emails.

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