Find duplicates in a column

What you will build

Detect duplicate customer IDs before sending a campaign. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

The sample Customer ID table selected in the current Google Sheets practice workbook.

Work through the example

Step 1: Keep the raw ID column unchanged in A2:A5.

Step 2: Enter =COUNTIF($A$2:$A$5,A2)>1 in H2, then fill the formula down through H5. The expected flags are TRUE, FALSE, TRUE, FALSE.

Formula to try

In the verified practice workbook, enter the formula in H2, then fill it down through H5. Keep A2:A5 unchanged and compare H2:H5 with the checkpoint.

=COUNTIF($A$2:$A$5,A2)>1

Duplicate flags in H2:H5 after COUNTIF formulas are filled down in Google Sheets.

How this formula works=COUNTIF($A$2:$A$5,A2)>1

Overall meaning of the whole formula
- Count how many times the value in A2 appears in A2:A5; return TRUE when it appears more than once to identify duplicates.
COUNTIF(range, criterion)
- `range` is required; it accepts the range to test. In this lesson the argument passed is `$A$2:$A$5`.
- `criterion` is required; it accepts a value, cell reference, or condition text to count. In this lesson the argument passed is `A2`.
Whole-expression evaluation
- COUNTIF(range, criterion) counts how many cells in the fixed list match the current row value.
- $A$2:$A$5 is absolute, so the comparison list does not move when the formula is filled down.
- A2 is relative, so each row checks its own value against the full list.
- >1 converts the count into TRUE for duplicated values and FALSE for values that appear only once.
Why the displayed final result appears
- >1 converts the count into TRUE for duplicated values and FALSE for values that appear only once.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Customer ID	Name	Duplicate?
C01	An	TRUE
C02	Binh	FALSE
C01	An Nguyen	TRUE
C03	Chi	FALSE

Expected result after filling the formula through H5: TRUE, FALSE, TRUE, FALSE. If the whole dataset lands in one cell, undo, select A1, and paste the tab-separated data again.

Common mistake and correction

If the result is unexpected, inspect the source cells, destination and referenced range in the formula. Duplicate IDs do not necessarily mean duplicate people; investigate spelling changes and imported leading zeros before deleting anything.

Independent practice

After following the example, complete this variation without editing the source data: Filter the TRUE flags and decide a documented resolution for each duplicate instead of removing whole rows blindly.

Key takeaways

✓Detect duplicate customer IDs before sending a campaign.
✓Keep the raw ID column unchanged in A2:A5.
✓Fill the duplicate-check formula from H2 through H5; the sample returns TRUE, FALSE, TRUE, FALSE.
✓Duplicate IDs do not necessarily mean duplicate people; investigate spelling changes and imported leading zeros before deleting anything.
✓Filter the TRUE flags and decide a documented resolution for each duplicate instead of removing whole rows blindly.

Continue learning

Previous lesson: Combine data from multiple sheets; Next lesson: Remove blank rows.

What you will build

Detect duplicate customer IDs before sending a campaign. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Work through the example

Step 1: Keep the raw ID column unchanged in A2:A5.

Step 2: Enter =COUNTIF($A$2:$A$5,A2)>1 in H2, then fill the formula down through H5. The expected flags are TRUE, FALSE, TRUE, FALSE.

Formula to try

In the verified practice workbook, enter the formula in H2, then fill it down through H5. Keep A2:A5 unchanged and compare H2:H5 with the checkpoint.

=COUNTIF($A$2:$A$5,A2)>1

How this formula works=COUNTIF($A$2:$A$5,A2)>1

Overall meaning of the whole formula
- Count how many times the value in A2 appears in A2:A5; return TRUE when it appears more than once to identify duplicates.
COUNTIF(range, criterion)
- `range` is required; it accepts the range to test. In this lesson the argument passed is `$A$2:$A$5`.
- `criterion` is required; it accepts a value, cell reference, or condition text to count. In this lesson the argument passed is `A2`.
Whole-expression evaluation
- COUNTIF(range, criterion) counts how many cells in the fixed list match the current row value.
- $A$2:$A$5 is absolute, so the comparison list does not move when the formula is filled down.
- A2 is relative, so each row checks its own value against the full list.
- >1 converts the count into TRUE for duplicated values and FALSE for values that appear only once.
Why the displayed final result appears
- >1 converts the count into TRUE for duplicated values and FALSE for values that appear only once.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Customer ID	Name	Duplicate?
C01	An	TRUE
C02	Binh	FALSE
C01	An Nguyen	TRUE
C03	Chi	FALSE

Expected result after filling the formula through H5: TRUE, FALSE, TRUE, FALSE. If the whole dataset lands in one cell, undo, select A1, and paste the tab-separated data again.

Common mistake and correction

Independent practice

Key takeaways

✓Detect duplicate customer IDs before sending a campaign.
✓Keep the raw ID column unchanged in A2:A5.
✓Fill the duplicate-check formula from H2 through H5; the sample returns TRUE, FALSE, TRUE, FALSE.
✓Duplicate IDs do not necessarily mean duplicate people; investigate spelling changes and imported leading zeros before deleting anything.
✓Filter the TRUE flags and decide a documented resolution for each duplicate instead of removing whole rows blindly.

Continue learning

Previous lesson: Combine data from multiple sheets; Next lesson: Remove blank rows.

Find duplicates in a column

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Find duplicates in a column

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments