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,"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
witzph1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

try

=IF(AND(COUNTIF($B$2:B2,B2)>1,A2=A1),"Duplicate","No")

It produces the correct result for your data sample.

Edit: Another option is to ensure the range for the Countif() starts with the first row of  the current LookupCode and extends to the current row. You can do that with

=IF(COUNTIF(INDEX(B:B,MATCH(A2,A:A,0)):INDEX(B:B,ROW()),B2)>1,"Duplicate","No")

The first index function INDEX(B:B,MATCH(A2,A:A,0)) finds the row with the first occurrence of the lookup code in the current row and returns the cell in column B of that row. The second index function returns the cell column B in the current row. The range operator ":" combines the two cells into a range that the Countif function can use.  

Step through the formula with the Formula Evaluation tool to see how the formulas get resolved into ranges. For example in row 18 of your sample file.

screenshot
cheers, teylyn
0
tomfarrarCommented:
What if you concatenated LookUp Code and First Name in a new column and then used =IF(COUNTIF($B$2:Bx,Bx)>1,"Duplicate","No") on the new column?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
witzph1Author Commented:
Teylyn, you first and more simple formula worked, but only if the LookupCodes were in exact order.  They would be in order almost always.  But your second, more complex formula worked no matter what the order.  

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.
0
witzph1Author Commented:
Thank you so much!
0
tomfarrarCommented:
Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.