Find duplicates based on a second column

Copper Contributor

Here is the situation. I have column A which shows account numbers. Column B shows what group that account belongs to (groups 1&2). I am trying to find the duplicates that belong to both groups. For example, I want to find the duplicate accounts where one of the accounts belongs to group 1 and that same account also belongs to group 2. Thank you for your help.

1 Reply

@lmorales1857 

I'd do this with helper columns like here

image.png

Variants:

in C2

=IF(COUNTIFS(A:A,A2,B:B,"<>"&B2),"Duplicated","")

in D2 (to show position of duplicated account)

=IFNA(MATCH(1,INDEX((A:A=A2)*(B:B<>B2),0),0),"")

I believe it could be other variants.