witzph1
asked on
Dynamic Duplicate Finder Formula for Excel
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,
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
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,
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much!
Glad to help.
ASKER
That second formula is a work of art!! I looked and looked at it, using the formula helper, the evaluator, etc. I've used match and index before, but the combo of everything takes a bit of thinking to wrap your head around what exactly it is doing. I'm impressed.
Tomfarrar, your idea actually worked perfectly also. Honestly, I tend towards simplicity and things I can understand, so I am leaning towards your idea. I know how to cancatonate and putting a formula to cancatonate in the top field and filling down with a double-click, then entering the formula I had proposed and was familiar with is very comfortable.
I'm going to split this one between the two of you since they are both great answers. I truly appreciate both of your help!! Thank you so much and have a great weekend.