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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Patient ID[/TD]
[TD]Date Recorded[/TD]
[TD]Country of Birth[/TD]
[TD]Error?[/TD]
[/TR]
[TR]
[TD]17038[/TD]
[TD]24-Jun-17[/TD]
[TD]ZZZ[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1497921[/TD]
[TD]27-Apr-17[/TD]
[TD]ZZZ[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1497921[/TD]
[TD]19-Jun-17[/TD]
[TD]ZZZ[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1497921[/TD]
[TD]14-Jun-17[/TD]
[TD]GBR[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1788532[/TD]
[TD]06-May-17[/TD]
[TD]GBR[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1788532[/TD]
[TD]30-Apr-17[/TD]
[TD]GBR[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Patient ID[/TD]
[TD]Date Recorded[/TD]
[TD]Country of Birth[/TD]
[TD]Error?[/TD]
[/TR]
[TR]
[TD]17038[/TD]
[TD]24-Jun-17[/TD]
[TD]ZZZ[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1497921[/TD]
[TD]27-Apr-17[/TD]
[TD]ZZZ[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1497921[/TD]
[TD]19-Jun-17[/TD]
[TD]ZZZ[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1497921[/TD]
[TD]14-Jun-17[/TD]
[TD]GBR[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1788532[/TD]
[TD]06-May-17[/TD]
[TD]GBR[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1788532[/TD]
[TD]30-Apr-17[/TD]
[TD]GBR[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]