• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

duplicate name differing ID finder

I need a formula to help identify cases where the name column (column C), appear more than once in a column of data, but only where the customer ID (column B) is different for these customers. If the customer name and ID is the same on each row that that name appears, that's not a true duplicate.

e.g.
ID--Name
123-J Bloggs
123-J Bloggs
456-A Bloggs
678-A Bloggs

should flag only the last 2 records as both have same customer name, but differing ID's. I then need to do it the other way round, where supplier ID is the same for however many rows but flag instances where the same ID shows 2 differing CUSTOMER names against that ID.
0
pma111
Asked:
pma111
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
ShumsDistinguished Expert - 2017Commented:
Try below in D2 and drag down:
=IF(COUNTIFS($B$2:$B$5,$B2,$C$2:$C$5,$C2)>1,"","Flag")

Open in new window

CountDupsChange the range accordingly.
1
 
Juan OcasioApplication DeveloperCommented:
Try this:
=IF(C2=C1,IF(B2<>B1,TRUE,FALSE),IF(C2=C3,IF(B2<>B3,TRUE,FALSE),FALSE))
0
 
pma111Author Commented:
first solution doesn't seem to work with the ranges changed, ithe first row I picked that was flag there was only 1 row in the data specific to that customer name so it cant be correct.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
pma111Author Commented:
2nd solution does work, but as per the question I'd like to also "I then need to do it the other way round, where supplier ID is the same for however many rows but flag instances where the same ID shows 2 differing customer names against that ID." - any guidance most welcome.
0
 
ShumsDistinguished Expert - 2017Commented:
Please upload sample/dummy workbook with expected result
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Formula suggested by Shums should work as per the condition you set to flag a duplicate.
Can you upload a sample workbook and show where does it not work?
0
 
Juan OcasioApplication DeveloperCommented:
"I then need to do it the other way round, where supplier ID is the same for however many rows but flag instances where the same ID shows 2 differing customer names against that ID."

=IF(B2=B1,IF(C2<>C1,TRUE,FALSE),IF(B2=B3,IF(C2<>C3,TRUE,FALSE),FALSE))
0
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now