CODEWITHSUNDEEP
excelexcel-formula
user3635596
user3635596

Reputation: 1

In Excel, How to check whether two columns are duplicate or not?

I have two columns in an excel sheet,
I want to check whether both columns are exactly same or not. Same I mean to say the sequence itself (Ax should be same as Bx).
For example,

A B
x1 x1
x2 x2
x3 x3
x4 y4
Here 4th column should be highlighted as x4 and y4 are not same.

Thanks

Upvotes: 0

Views: 122

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34230

You could also use an array formula to find the first row (if any) where the two cells don't match:-

=IFERROR("Difference in row "&MATCH(TRUE,A:A<>B:B,0),"No Difference")

(must be entered with Ctrl Shift Enter )

Upvotes: 0

Brino
Brino

Reputation: 2502

Three options:

Conditional Format

If you just want to highlight differences, add a conditional format.

  1. Highlight columns A and B
  2. Add A conditional format highlight rule > more rules
  3. Use a custom formula
  4. =if($A1<>$B1,1,0)
  5. Add a fill format to highlight your cells

Formula

If you want to know whether or not ANY cell in column A or B is different, you

  1. Add the formula =if(not(A1=B1),1,0) to cell C1
  2. copy this cell and paste to column C
  3. Add the formula =if(sum(C:C)>0,'NOT EQUAL','EQUAL') to D1
  4. D1 will now tell you whether column A = B

MACRO

Add a macro and do something with it. This macro will just paste result into cell E1 and display a message box when you run it. 

Sub ColumnCheck()
Dim i, LastRow, LastRowA, LastRowB, response
'get last row in column A and B, rather than 
'iterating through all possible rows
LastRowA = Range("A" & Rows.Count).End(xlUp).Row
LastRowB = Range("A" & Rows.Count).End(xlUp).Row
'get the greatest row between A and B
If LastRowA >= LastRowB Then
    LastRow = LastRowA
Else
    LastRow = LastRowB
End If
'iterate through all rows comparing them.
For i = 1 To LastRow
    If Cells(i, "A").Value <> Cells(i, "B").Value Then
         response = "The columns are not equal!"
         Range("E1") = response
         MsgBox (response)
         Exit Sub
    End If
Next
response = "The columns are equal!"
Range("E1") = response
MsgBox (response)

End Sub

Upvotes: 1

Related Questions