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...?
Ganesh STech Lead cum developerAsked:
Who is Participating?
 
OddyWriterMr.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
 
Rob HensonFinance AnalystCommented:
Not sure how you are determining duplicate.

Repeated in the same column or occurring in both columns??
0
 
Rob HensonFinance 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ganesh STech Lead cum developerAuthor Commented:
done.working..
0
 
Rob HensonFinance AnalystCommented:
If you want to remove duplicates, there is the Duplicate removal tool in the Data group.
1
 
Ganesh STech Lead cum developerAuthor Commented:
ok..thanks..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.