Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
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 alphanumeric values in two columns in Excel?
Many times, we need to compare and identify the duplicate entries available in various columns of the excel data. Searching the duplicate entry of each value one by one is a tedious task. This can be achieved through a formula combination of excel sheet. Let’s see how the same can be achieved using the below steps.
Compare alphanumeric values in two columns to find the same duplicates
Step 1: We have taken the following sample data for comparison.
Step 2: Enter the formula in C2 cell as mentioned below. This formula will return Duplicate for those values which are equal or
=IFERROR(IF(MATCH(A2,$B$2:$B$13,0)>0,"Duplicate"),"")
Step 3: Now, drag the formula till the last row of data and the final output will be as following.
Formula Syntax Description
| Argument | Description |
|---|---|
| IFERROR (value, value_if_error) | Value the argument that needs to be checked for errors. Required Value_if_error the value to be returned if error occurs. Required |
| MATCH (lookup_value, lookup_array, [match_type]) | Lookup value is the value that needs to be matched in look up array. Required Look up array is the range of cells in which lookup value to be searched. Required Match type specifies what type of match to be performed. 1 is for larger values, 0 stands for exact values and -1 stands for lesser values. |
Note: In above formula, A2 and B2 are the two numbers that are being compared, duplicate is being returned against the values of column A.
Conclusion
Hence using the above formula, two values can be compared for duplicity. One value can be compared with multiple range arrays as well. Using these steps, you can filter your dataset and create MIS reports as per the requirement. Keep learning excel.
501 Views
