## How To Compare Two Columns In Excel For Matches?

Asked By 0 points N/A Posted on -

I have entered some data on my excel sheet, and I am afraid I have Written some duplicate values in two different attributes. Can I compare two columns in excel for matches?

SHARE
Answered By 0 points N/A #162681

## 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