Identifying all rows where conflicting entries exist

[BigRods]

Hi,

I'm looking for a way of identifying all entries for a Patient ID where the Patient ID's have conflicting "Country of Birth" entries recorded.

Entries for each Patient ID may all say either ZZZ or GBR, but they cannot say both.
The problem with identifying them being that:

Rows may list the country of birth in this order (easy):
ZZZ
GBR

or they may be as follows:
ZZZ
ZZZ
GBR

ZZZ
GBR
ZZZ
(i.e not in a set order)

I need to find a formula that enters "1" in column D for all entries for that Patient ID that have any conflicting data in there - is this possible? Alternately, if there is an easier way of identifying all records? Hope this makes sense!


My data looks as follows - ID's 17038 and 1788532 are fine, but 1497921 needs to be identified as there are conflicting entries recorded:

Patient ID	Date Recorded	Country of Birth	Error?
17038	24-Jun-17	ZZZ	0
1497921	27-Apr-17	ZZZ	1
1497921	19-Jun-17	ZZZ	1
1497921	14-Jun-17	GBR	1
1788532	06-May-17	GBR	0
1788532	30-Apr-17	GBR	0

<tbody>
</tbody>

 

[MickG]

If you fail to get a formula response , try this code ,it will do what you want !!!!

[COLOR="Navy"]Sub[/COLOR] MG09Aug16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Array(Dn.Offset(, 2).Value, 0, Dn)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
            [COLOR="Navy"]If[/COLOR] Not Q(0) = Dn.Offset(, 2).Value [COLOR="Navy"]Then[/COLOR] Q(1) = 1
            [COLOR="Navy"]Set[/COLOR] Q(2) = Union(Q(2), Dn)
        .Item(Dn.Value) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    .Item(K)(2).Offset(, 3) = .Item(K)(1)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[BigRods]

Wow that's brilliant, works a treat - thanks so much

 

[MickG]

You're welcome