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

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Finance AnalystCommented:
Not sure how you are determining duplicate.

Repeated in the same column or occurring in both columns??
0
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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