How To Compare Two Rows In Excel For Differences

This is an interesting state of affairs that often comes upwards. Namely, sometimes one needs to differentiate information into 2 different columns. There are so many processes in which Excel compare two lists and render differences. In this article, we will see the ways on how to compare ii columns in Excel for differences.

So, let'south get started with a simple example, to illustrate how to accomplish this.

Introduction

Here we have two lists where some fruits name is placed. Nosotros volition compare the two lists for finding the differences. The ii lists containing the fruits name is given below.

Nosotros will run into 4 dissimilar processes of finding the differences between the ii columns. In every process, we will use the same table.

Compare Two Columns in Excel For Differences using Conditional Formatting

We tin apply conditional formatting to highlight the unique values of two columns. The procedure is simple and given below.

• Get-go, select the ranges where you want to utilise the provisional formatting. In this example, the range is A2:B8

• Now, in the Dwelling Tab click on the Conditional Formatting and Under Highlight Cells Rules click on to Duplicate Values.

• In the Indistinguishable values Dialogue Box if you lot select Duplicate you will see the duplicate values of the two cells.

• If yous select Unique in the Duplicate values Dialogue Box you will see the unique values of the two cells.

• Press OK to confirm the conditional formatting.

Read More: How to Compare Two Columns or Lists in Excel

Compare 2 Columns in Excel Using VLOOKUP

Nosotros can use the VLOOKUP function in Excel to notice the differences betwixt two lists or columns. The process is given below.

• In Prison cell C2 use the formula

  =IF(ISNA(VLOOKUP(A2,$B$2:$B$8,1,0)),"NO","YES")

  and printing Enter.

• After pressing Enter, you will see the statement NO in cell C2. That is considering the fruit name Apple from List-1 is non available in Listing-2.

• Now drag down the formulated prison cell downwards from C2 to C8 to run across the differences between List-ane and List-ii.

Read More: How to Compare 2 Columns in Excel Using VLOOKUP

Compare Two Columns in Excel & Returns the Difference

Here we will be using IF, ISERROR, and MATCH functions to compare ii columns. We will compare Listing-1 with List-ii. The formula will calculate the 2 lists and will return the fruit's name which is only in List-1. The process is given below.

• In Cell C2, write the formula

  =IF((ISERROR(MATCH(A2,$B$2:$B$xiv,0))),A2,"")

  and press enter.

• After pressing Enter you volition see the name of the fruit Apple tree which is merely placed in List-one.

• Now drag down this formulated cell to meet the fruit'southward name which is only in List-ane.

• In this aforementioned way, y'all tin can find the fruit'southward proper noun which is only in List-two. In that example, the formula will be,

  =IF((ISERROR(Match(B2,$A$2:$A$15,0))),B2,"")

  which will exist written in cell D2 and should be dragged down to detect the comparison betwixt the two lists.

How to Compare Two Columns in Excel For Differences Using Formula

In this procedure, if List-1 contains any fruit name which is not placed in List-two, the formula that we will be using volition say that the fruit name from List-i is not institute in List-2. Let`due south commencement the comparison.

• In jail cell C2 write the formula

  =IF(COUNTIF($B$2:$B$8, $A2)=0,                "Not Plant in List-2",                "")

  and printing enter.

• After pressing Enter you will see the argument, Not Found in List-2 has appeared. It is because the fruit name Apple from List-1 is not in Listing-two.

• At present, drag down the formulated cell C2 downwards to copy this formula for the residuum of the cells of column C. By doing this, you will see the differences betwixt the two columns.

Download Working Files

Decision

So, nosotros can run across the unlike processes of finding the differences between the two columns. The comparison between two columns can be obtained for matches also. Out of the 4 procedures we discussed using conditional formatting is the best way to compare between ii columns. Because in conditional formatting you can compare between multiple columns, the procedure is simple and fast and you lot tin find both matches and differences.

Hope you observe this article helpful. Browse our website for finding more useful manufactures.

---

Farther Readings

• How to Compare Two Columns Using Conditional Formatting in Excel
• Excel Compare Text in Two Columns (7 Fruitful Ways)
• Excel Compare Two Cells Text (nine Examples)
• Excel formula to compare 2 columns and return a value (v examples)

Source: https://www.exceldemy.com/how-to-compare-two-columns-in-excel-to-find-differences/

Posted by: parkreakes2000.blogspot.com

Share this post