This guide shows you how to identify duplicate values that appear in two different columns in Google Sheets. Whether you’re comparing two lists of emails, product IDs, or names, you’ll learn several methods to highlight or extract those matches. You’ll need a Google account and a spreadsheet with the two columns you want to compare.
What You’ll Need
- A Google account (free) to access Google Sheets.
- A spreadsheet with the two columns you want to compare.
- Basic familiarity with Google Sheets (entering data, selecting cells).
Step-by-Step
- Open your Google Sheet and locate the two columns you want to compare. For this guide, we’ll assume Column A (rows 1–10) and Column B (rows 1–10) contain the lists.
- Decide on your goal: Do you want to highlight duplicates in place, extract a list of matches, or count them? The steps below cover all three.
- Method 1: Highlight duplicates with conditional formatting.
- Select the range in the first column (e.g., A1:A10).
- Click Format → Conditional formatting.
- Under “Format cells if,” choose Custom formula is.
- Enter the formula:
=COUNTIF($B$1:$B$10, A1) > 0(adjust ranges to match your data). - Set a formatting style (e.g., light red fill) and click Done.
- Repeat for Column B: select B1:B10, add another rule with
=COUNTIF($A$1:$A$10, B1) > 0.
- Method 2: Extract a list of duplicates.
- In a new column (e.g., D1), enter the formula:
=FILTER(A1:A10, COUNTIF(B1:B10, A1:A10) > 0). - Press Enter. This will list all values from Column A that appear in Column B.
- To get unique values from that list, wrap with
UNIQUE:=UNIQUE(FILTER(A1:A10, COUNTIF(B1:B10, A1:A10) > 0)).
- In a new column (e.g., D1), enter the formula:
- Method 3: Count the number of duplicates.
- Use
=SUMPRODUCT(COUNTIF(B1:B10, A1:A10))to count total matches (including repeats). - For a count of unique matches, use
=COUNTA(UNIQUE(FILTER(A1:A10, COUNTIF(B1:B10, A1:A10) > 0))).
- Use
- Method 4: Use a pivot table (for large datasets).
- Select both columns (including headers) and go to Data → Pivot table.
- In the pivot editor, add both columns to “Rows.”
- Add any column to “Values” and set it to COUNTA.
- Rows that appear more than once across the combined data are duplicates. You can filter to show only counts > 1.
Common Mistakes
- Mismatched ranges: If your ranges are different sizes (e.g., A1:A10 vs B1:B20), the formulas may return errors or miss matches. Always ensure ranges cover all data.
- Leading/trailing spaces: Extra spaces in cells will make identical text appear different. Use
=TRIM()to clean data first. - Case sensitivity: By default, COUNTIF is case-insensitive. If you need case-sensitive matching, use
=EXACT()in an array formula. - Using absolute vs relative references: In conditional formatting, forgetting the dollar signs ($) can cause the formula to shift incorrectly.
Tips
- Use named ranges (e.g., name A1:A10 as “ListA”) to make formulas easier to read and maintain.
- Before comparing, sort both columns alphabetically to visually spot duplicates quickly.
- If your data has numbers stored as text, convert them to numbers using Data → Split text to columns or the VALUE() function.
- For very large datasets, consider using Google Apps Script for faster processing, but the built-in formulas are sufficient for most cases.
Troubleshooting
- Formula returns #N/A: This usually means the FILTER function found no matches. Check that your ranges are correct and that the data actually contains duplicates.
- Conditional formatting doesn’t highlight anything: Ensure the custom formula is entered correctly, with the first cell reference relative (e.g., A1, not $A$1). Also, make sure the range you selected matches the formula’s row.
- Results include blank cells: If your columns have empty cells, COUNTIF will treat them as matches. Add a condition to exclude blanks:
=FILTER(A1:A10, (COUNTIF(B1:B10, A1:A10) > 0) * (A1:A10 <> "")). - Duplicates are not detected due to formatting: Numbers formatted as text (e.g., “001” vs 1) will not match. Use
=VALUE()to convert or reformat the column.
Frequently Asked Questions
How do I find duplicates in the same column?
Use conditional formatting with the formula =COUNTIF(A:A, A1) > 1 applied to the entire column. This highlights all values that appear more than once.
Can I compare more than two columns?
Yes, you can extend the COUNTIF formula to include multiple columns. For example, to find values in Column A that appear in either Column B or C, use =COUNTIF(B:C, A1) > 0.
How do I remove duplicates after finding them?
Select the range, go to Data → Data cleanup → Remove duplicates. Choose which columns to consider and click “Remove duplicates.”
What if I want to compare two columns on different sheets?
Use sheet references in the formula. For example, =COUNTIF(Sheet2!B:B, A1) will check if the value in A1 appears in column B of Sheet2.
Is there a way to highlight duplicates in both columns at once?
Yes, use the same conditional formatting rule on both columns. For example, select A1:B10 and apply the formula =COUNTIF($A$1:$B$10, A1) > 1. This will highlight any value that appears more than once in the combined range.
Why does my FILTER formula return an error?
Common reasons: mismatched array sizes, non-array inputs, or the formula being entered in a cell that already has data. Ensure you enter the formula in an empty column and that the ranges have the same number of rows.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- How to Factory Reset iPad If You Forgot Password
- Ubuntu in Windows Store: Which Editions Work?
- Secure Guest Account on Windows 11: Step-by-Step
- Stop Outlook From Changing Hyperlink Font
- Hiberfil.sys: What It Is & How to Delete It
- How to Make a Mask on Snapchat: Step-by-Step