How to Find Duplicate Values Shared Between Two Excel Columns
Question details
The user needs to identify, extract, or highlight duplicate values that exist across two separate columns, specifically excluding duplicates that only appear within a single column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two data lists to isolate and identify common intersection items shared across both columns.
- Observed behavior
- Standard duplicate highlighting features flag all repeated values within the entire selection, making it difficult to pinpoint only the values shared between two distinct columns.
Ensure your data does not contain hidden trailing spaces, as formulas treat text with and without spaces differently. You can use the TRIM function to clean your data ranges before comparing them.
Extract Common Values Using Dynamic Array Formulas
Use a combination of UNIQUE, FILTER, and COUNTIF functions to pull out a clean list of values that appear in both columns.
This method is ideal when you want to generate a brand-new list of shared values rather than just highlighting the existing data. It automatically spills the matching results into adjacent cells.
Click on an empty cell where you want your list of common items to begin (for example, cell E2).
Enter the formula: =UNIQUE(FILTER(A2:A6,COUNTIF(C2:C4,A2:A6)>0)). Ensure you replace A2:A6 and C2:C4 with the actual ranges of your two columns.
Press Enter. The formula will calculate the intersections and dynamically list all unique shared values below the selected cell.
Highlight Shared Duplicates Using Conditional Formatting
Apply a custom conditional formatting rule utilizing the MATCH function to visually highlight intersecting values directly within the columns.
Find Shared Duplicates Easily with WPS Spreadsheet
WPS Office provides a highly compatible spreadsheet tool that seamlessly runs complex dynamic array formulas and advanced conditional formatting rules. You can quickly cross-reference two columns and identify matching data without needing third-party add-ins.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your two columns of data.
- 2. Select the data range: Highlight the cells in the first column that you wish to compare against the second column.
- 3. Access Conditional Formatting: Go to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.
- 4. Apply the cross-reference formula: Choose the formula option, input your MATCH formula comparing the two columns, select a highlight color, and click OK.
Frequently Asked Questions
Why does the standard 'Highlight Duplicate Values' feature not work for two columns?
The built-in 'Duplicate Values' preset highlights any value that appears more than once across the entire selected range. It cannot differentiate between a duplicate that exists twice in Column A versus a duplicate that is shared between Column A and Column B.
Does the MATCH formula work for text with different capitalizations?
No, the standard MATCH function is case-insensitive, meaning 'Apple' and 'apple' will be considered duplicates. If you need a strict case-sensitive comparison, you will need an array formula combining the EXACT function.
Can I find duplicates without using formulas?
Yes, you can use the Power Query tool to merge two tables using an 'Inner Join', which will only keep the rows that match in both lists. However, conditional formatting remains the fastest visual method for everyday tasks.
How do I remove the shared duplicates after finding them?
Once you have highlighted them using conditional formatting, you can right-click a cell, choose to filter the column by the cell's color, select all the visible colored rows, and delete them. Alternatively, use the UNIQUE/FILTER extraction method to generate a brand-new list.
