How To Compare Two Columns In Excel For Matches?

How To Compare Two Columns In Excel For Matches?

Yes, you can compare the columns in excel to find out any duplicate value by two methods:

i. Using a formula

• Open Excel, and in anew sheet, fill the following data:

A1: 1 C1:1

A2: 2 C2:2

A3: 3 C3:3

A4: 4 C4:4

Leave the column B empty.

Now, type the following formula in B1 address bar:

=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)).””A1)

Then, select all cells from B1 to B5.

Finally, Click on fill in the editing pane, and then click on

Down.

ii. Using Visual Basic macro

• Open Excel, then press Alt+F11 to start visual basic

• Click on Insert, and then Module

• Now, enter the following code:

•  Sub Find_Matches()
•  Dim CompareRange As Variant, X As Variant, Y As Variant
•  Now, set CompareRange equal to the range to which you want to compare the selection.
•  Set CompareRange = Range("C1:C5")
•  Go through each cell and compare it toeach cell in CompareRange.
•  For Each X In Selection
•  For Each Y In CompareRange
•  If X = Y Then X.Offset(0, 1) = X
•  Next Y
•  Next X
•  End Sub

• Now, press Alt+F11 again to return to Excel

• Enter the data in Column A and C same as in method 1.

• Select A1 to A5

• Now, go to macros and click on Find_Matches, then click on Run.

You can see the duplicate values in column B.