# EXCEL - How to check duplicate values from two columns.

Hi.

How to find duplicate when we compare two columns.

i need excel formula to apply this.

Col1      Col2      Result
101      100      duplicate
101      80
103      100
101      100      duplicate
103      300
what is the exact exact formula for this...?
###### Who is Participating?

Mr.Commented:
1. You have to sort data in Col1 then Col2 (Example in Img1)
2. In C1, =IF(AND(A1=A2,B1=B2),"Duplicated","") (Example in img2)
3. Copy formula down.
pantip1.png
pantip2.png
0

Finance AnalystCommented:
Not sure how you are determining duplicate.

Repeated in the same column or occurring in both columns??
0

Finance AnalystCommented:
Looking at it, I think you are after duplicates of the combination of value in column 1 AND value in column 2. If so, no need to sort the data but use this formula in column 3:

=IF(COUNTIFS(A:A,A2,B:B,B2)>1,"Duplicate","")

where A:A is column 1 and B:B is column 2; copy down column 3 as far as required.

Thanks
2

done.working..
0

Finance AnalystCommented:
If you want to remove duplicates, there is the Duplicate removal tool in the Data group.
1