- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to compare two columns and delete matches in Excel?
If you want to compare two or more columns to find duplicate values, it can be done using a formula mentioned in this article. Many times, we come across a data set where values are entered repeatedly and required to be filtered. Let’s see how this can be achieved.
Compare Two or More Columns Using a Formula
Step 1 − We have taken sample data as shown below having two columns with some duplicate values.
Step 2 − In the column C, we will identify the duplicate and unique values using the below formula.
Step 3 − Now, in cell C3 enter the above−mentioned formula and drag it to the last row till which data needs to be compared.
Formula Syntax Description
IsError ( expression )
MATCH(lookup_value, lookup_array, [match_type])
Step 4 − After dragging the formula till the 9th row, the output will be as follows.
Note − In this sample data, we are comparing List 2 with List 1. Formula will display Duplicate against those records which are available in List 2 as well as List 1.
Delete Duplicate Values
After identifying the duplicate values, they can be deleted at once using the following steps.
Step 1 − Now, select the column, where duplicate and unique results have been displayed and go to Home tab > Editing Tools > Sort & Filter > Filter.
Step 2 − On click of Filter, a filter will be created in the respective column. Now click the filter arrow and select ‘Duplicate’ option only. Then click OK.
Step 3 − Now it will display only the duplicates, select the values of List2 column, and press Delete key. This will delete the duplicate values available in List2 as compared to the List1.
Step 4 − Now remove the filter and the output will be as following.
In this way multiple sheets can be compared against 1 sheet and duplicate values can be deleted from all other lists. This method is helpful to get the unique value throughout the data.
Kickstart Your Career
Get certified by completing the courseGet Started