troubleshooting Question

Dynamic Duplicate Finder Formula for Excel

Avatar of witzph1
witzph1 asked on
Microsoft OfficeMicrosoft Excel
5 Comments2 Solutions364 ViewsLast Modified:
I have a huge spreadsheet of names exported from the contacts of a management system.  Within the management system a particular client could have multiple contacts associated with it.  For instance an account might have a contact for a husband, a wife, and two children, totaling four contacts.  When exported, each of the four contacts would have the same "LookupCode" or "Account Code" in the first column.  

For reasons I won't go into, we had a lot of contacts duplicated within our system.  In the example above, perhaps the contact for the father, I'll use "Bob" as his first name, was duplicated, so the total contacts in that account are 5, two "Bobs," one wife and two children.  Only the one duplicate "Bob" is wrong.  And if "Bob" occurs under 50 other accounts, those aren't duplicates.

I want to export all of our contacts to a spreadsheet and mark the contacts which are duplicate contacts.  In my exported spreadsheet I want a formula in a column titled "Duplicate" which returns "No" if it's not a duplicate within the Account "LookupCode" and "Duplicate" if it is:

LookupCode        FirstName    Duplicate      Why?
CodeA                Bob                  No              1st instance of Bob with CodeA
CodeB                Bob                  No              1st instance of Bob with CodeB
CodeB                Bob               Duplicate      2nd instance of Bob with CodeB

I've used a formula to eliminate duplicates in a list before which looks like:

=IF(COUNTIF($B$2:Bx,Bx)>1,"Duplicate","No")

But for my needs the formula can't look for Bob's in the whole of column B, but only as far up as the first instance of the code in column A.  Makes sense?  

I've attached a spreadsheet showing the real thing.
Test.xlsx
ASKER CERTIFIED SOLUTION
Tom Farrar
Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros