CODEWITHSUNDEEP
excelexcel-formula
eag
eag

Reputation: 15

Find Rows that have duplicates in two separate columns

I have two address books that have many shared contacts between them. I am trying to find all contacts in both lists that are only listed in one and not both. So far I have merged both contact lists in excel, and created a new column with the function AND(COUNTIF(B:B,B2)>1, COUNTIF(D:D,D2)>1). (Col B is first names and Col D is last names. Essentially, I am trying to get a new col to list FALSE if the first and last name combination is listed only once on the merged list.

However, what I have found is that this does not work because if the first name is found somewhere on the list, and the last name is found somewhere on the list, it will produce FALSE even if the specific COMBINATION of first name/last name is not found elsewhere on the list. Can anyone help me narrow down my argument to produce what I'm looking for?

Upvotes: 0

Views: 50

Answers (2)

user10862412
user10862412

Reputation:

Put both checks together with COUNTIFS.

=COUNTIFS(B:B, B2, D:D, D2)>1

Upvotes: 0

BigBen
BigBen

Reputation: 49998

Use COUNTIFS to count the instances of the combined first and last names. Something like this:

=COUNTIFS(B:B,B2,D:D,D2)>1

enter image description here

Upvotes: 1

Related Questions