incorrect duplicate values

[coolcarnee]

Hi,


I am working through a huge list of incorrect duplicate values. To explain it further the list has some medication names and the corresponding unique alphanumeric code. I have to flag all the instances where the medication name is duplicated but the alphanumeric code is not same. The data would look something like this


Paracetamol A567

Medication2 A754

Medication3 A986

Paracetamol A567

Medication4 A876

Paracetamol A965

Medication5 A854


Now in the above example the second last entry should have been Paracetamol A567. I have been able to isolate the duplicate values using COUNTIF formula but could get my head around how to progress further to flag wrong entries.


Can anyone suggest. Thanks for your help.


coolcarnee

 

[Hui]

Coolcarnee


I assume that the Medication and Code are in different columns

If medication in Col A and Code in B paste this in C1 and copy down

Change the value $7 as appropriate

=+SUMPRODUCT(1*($A$1:$A$7=A1)*($B$1:$B$7<>B1))

It will show you when there are other values of a code that don't match the current code.

In your example it will show a 1 in C1, indicating that there is one other Code that doesn't match the current code for the current medication.

When there is a unique code it will show a Zero