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
Compare two columns and list differences with formulas.
For instance, we have two columns in a data set having some names. And now we want to identify what names are repeating in either list. For this, we can apply a formula in two other columns, to get the unique values of both the columns. Let’s see how we can achieve this.
Compare two columns and list differences with formulas
Step 1: We have taken the following sample data for comparison.
Step 2: Enter the formula in E2 cell as mentioned below. This formula will return the unique names of column 1, which are not available in column 2.
=IF((ISERROR(MATCH(A2,$B$2:$B$14,0))),A2,"")
Step 3: Now, enter the below formula in F2 cell. This formula will return the unique names of column 2, which are not available in column 1.
=IF((ISERROR(MATCH(B2,$A$2:$A$13,0))),B2,"")
Step 4: Now, drag the formula till the last row of data and the final output will be as following. The formula will display those names which are unique.
Formula Syntax Description
| Argument | Description |
|---|---|
| IF (logical_test, {value_if_true}, {value_if_false}) | Logical_test specifies the condition basis which the data needs to be rendered. Value_if_true specifies the value that shall be returned if the condition satisfies. Value_if_false specifies the value that shall be returned if the condition does not satisfy. |
| ISERROR (expression) | Expression can be any valid logical formula or value. |
| 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: Here, Unique in Column 1 displays the values which are not available in Column 2 and Unique in Column 2 displays the values which are not available in Column 1.
Conclusion
Hence using the above formula, value of one column can be returned in another column after comparing with other column. Data of one column can be compared with multiple ranges as well. Keep learning excel.
68 Views
