CODEWITHSUNDEEP
excel
Ted
Ted

Reputation: 23

Excel: Find duplicates in one column, then remove rows based on value in other column

I've been able to find a number of articles that seem to orbit my particular puzzle, but I'm having difficulty carving out the specific solution for it. Using the below image for reference:

ID  Name      Company Name
 5  Dennis E  Lantz Boggio Architects, Pc
 6  Director  Lantz Boggio Architects, Pc
 7  Glenn D   Lantz Boggio Architects, Pc
 8  Director  Ge Johnson Construction
 9  Evan Da   GH Phipps Construction Companies
10  Paul Fog  GH Phipps Construction Companies
11  Todd W    GH Phipps Construction Companies

I have a mailing list that is organized so each unique contact is placed on an individual row. The list contains columns for Name (column A in my sheet) and Company Name (column B).

If the Name cell was originally empty, a default 'generic' title is entered (e.g. 'Director', as per rows 6 and 8 in the image).

In some cases, there are multiple contacts at the same company (e.g. rows 5-7, 9-11). Occasionally, one of those contacts has a 'generic' name (e.g. row 6).

What I'd like to do:

So in the example image, only row 6 would be deleted because Column B contains a duplicate address, and Column A contains the value 'Director'.

Thank you!

Upvotes: 2

Views: 833

Answers (1)

pnuts
pnuts

Reputation: 59442

Maybe, in C5 and copied down to suit:

=AND(COUNTIF(B:B,B5)>1,A5=C$1)

with Director in C1.

Then filter ColumnC to select TRUE and delete.

COUNTIF(B:B,B5) searches for the content of B5 throughout ColumnB (the B:B) and returns the count of the instances. B5 is within ColumnB so function will always find at least 1, for duplicates more than one, so >1 should detect that the row in question (5 for example) is not the only instance.

However, similar entries will not be counted - for example those that end in a trailing space, when what is in B5 does not.

Upvotes: 1

Related Questions