Hello,

I am working on an Excel database which is used to record patients who
accessed a service. There are 12,000+ rows in the database.

The database is designed in a such a way that patients have a seperate
record (row) for each month that they used the service. Each patients has a
unique PatientID number, however, many patients have more than one number and
I want to identify these patients, but do not know how and would apprecaite
your help in doing this.

The datbase is in the following format:

Month PatientID SurnameForename D.O.B Address etc......
Jun05 15678 BloggsJoe 23/4/07 Any Street
Apr05 15745 AnyOne 15/8/90 Whereever
Jan05 15896 BloggsJoe 23/4/07 Any Street

As you can see from the above example Joe Bloggs has two entries, one for
June and one for January but they have different PatientIDs (which they
should not). I would like to filter the 12,000 records or run a pivot to
identify patients which have multiple PatientIDs. Is there anyway that I can
do this?

Thank you in advance, Richard

The short answer to your question is that this is a perfect example of why
Excel shouldn't be used as a database--it's too easy to corrupt data and lead
to inquiries like that.

The long, and awkward, answer to your question:
1) you can sort by SurnameForename and then do a visual inspection.

2) You can do a pivot table in which you Sort by SurnameForename and then
PatientID. Assuming no two different patients have the same name (doubtful,
given your patient population size) you can identify unique patients who have
multiple PatientIDs.

Perhaps someone can come up with a more elegant solution, but a proper
database, such as Access, really is your best bet here. If this is for a
business, you may want to invest in a database consultant to design a
patient-records system for you.

Dave
--
Brevity is the soul of wit.


"richardwo" wrote:

Hello,

I am working on an Excel database which is used to record patients who
accessed a service. There are 12,000+ rows in the database.

The database is designed in a such a way that patients have a seperate
record (row) for each month that they used the service. Each patients has a
unique PatientID number, however, many patients have more than one number and
I want to identify these patients, but do not know how and would apprecaite
your help in doing this.

The datbase is in the following format:

Month PatientID SurnameForename D.O.B Address etc......
Jun05 15678 BloggsJoe 23/4/07 Any Street
Apr05 15745 AnyOne 15/8/90 Whereever
Jan05 15896 BloggsJoe 23/4/07 Any Street

As you can see from the above example Joe Bloggs has two entries, one for
June and one for January but they have different PatientIDs (which they
should not). I would like to filter the 12,000 records or run a pivot to
identify patients which have multiple PatientIDs. Is there anyway that I can
do this?

Thank you in advance, Richard

Richard,

In a new column, say Y, concatenate any cells that you think will help
uniquely identify a patient. eg =C1&D1. This will produce BloggsJoe24/4/07
in Y1.

Copy down the formula for all rows.

In Z1 type =IF(COUNTIF($Y$1:Y1,Y1)=1,COUNTIF($Y$1:$Y$12000,Y1 ),"") and copy
down as above.

This says: if this is the first occurance of the identifyer in the column
then count how many times this identifyer occurs in the whole column
otherwise leave the cell empty.

You then need to review the Z column for entries greater than 1

Fred
"richardwo" wrote:

Hello,

I am working on an Excel database which is used to record patients who
accessed a service. There are 12,000+ rows in the database.

The database is designed in a such a way that patients have a seperate
record (row) for each month that they used the service. Each patients has a
unique PatientID number, however, many patients have more than one number and
I want to identify these patients, but do not know how and would apprecaite
your help in doing this.

The datbase is in the following format:

Month PatientID SurnameForename D.O.B Address etc......
Jun05 15678 BloggsJoe 23/4/07 Any Street
Apr05 15745 AnyOne 15/8/90 Whereever
Jan05 15896 BloggsJoe 23/4/07 Any Street

As you can see from the above example Joe Bloggs has two entries, one for
June and one for January but they have different PatientIDs (which they
should not). I would like to filter the 12,000 records or run a pivot to
identify patients which have multiple PatientIDs. Is there anyway that I can
do this?

Thank you in advance, Richard

Thank you Fred for your quick response. I have tried this but it doesn't
work. I better clarify that in the database patients will be entered onto
multiple rows (for example, John Smith may have used the service in March
2006, through to September 2006 and will thus have 7 entries (rows) on the
database, one for each month. These should all carry the same unique patient
identifier number. In the database John Smith should have just one unique ID
number, but many of the records have multiple numbers. Back to the example
say Johns identifier number is 245, this number should be entered on each row
(month) that John uses the service, however, say in June the number given is
394. I need to identify patients such as John who have more than 1 identity
number.

Thanks again, Richard

"Fred Davis" wrote:

Richard,

In a new column, say Y, concatenate any cells that you think will help
uniquely identify a patient. eg =C1&D1. This will produce BloggsJoe24/4/07
in Y1.

Copy down the formula for all rows.

In Z1 type =IF(COUNTIF($Y$1:Y1,Y1)=1,COUNTIF($Y$1:$Y$12000,Y1 ),"") and copy
down as above.

This says: if this is the first occurance of the identifyer in the column
then count how many times this identifyer occurs in the whole column
otherwise leave the cell empty.

You then need to review the Z column for entries greater than 1

Fred
"richardwo" wrote:

Hello,

I am working on an Excel database which is used to record patients who
accessed a service. There are 12,000+ rows in the database.

The database is designed in a such a way that patients have a seperate
record (row) for each month that they used the service. Each patients has a
unique PatientID number, however, many patients have more than one number and
I want to identify these patients, but do not know how and would apprecaite
your help in doing this.

The datbase is in the following format:

Month PatientID SurnameForename D.O.B Address etc......
Jun05 15678 BloggsJoe 23/4/07 Any Street
Apr05 15745 AnyOne 15/8/90 Whereever
Jan05 15896 BloggsJoe 23/4/07 Any Street

As you can see from the above example Joe Bloggs has two entries, one for
June and one for January but they have different PatientIDs (which they
should not). I would like to filter the 12,000 records or run a pivot to
identify patients which have multiple PatientIDs. Is there anyway that I can
do this?

Thank you in advance, Richard

Thanks Dave

Appreciate your advice. I'm in complete agreement that Access should have
been used to create the database. Unfortunately, the database was designed by
an external organisation and I'm stuck with it.

Cheers

"Dave F" wrote:

The short answer to your question is that this is a perfect example of why
Excel shouldn't be used as a database--it's too easy to corrupt data and lead
to inquiries like that.

The long, and awkward, answer to your question:
1) you can sort by SurnameForename and then do a visual inspection.

2) You can do a pivot table in which you Sort by SurnameForename and then
PatientID. Assuming no two different patients have the same name (doubtful,
given your patient population size) you can identify unique patients who have
multiple PatientIDs.

Perhaps someone can come up with a more elegant solution, but a proper
database, such as Access, really is your best bet here. If this is for a
business, you may want to invest in a database consultant to design a
patient-records system for you.

Dave
--
Brevity is the soul of wit.


"richardwo" wrote:

Hello,

I am working on an Excel database which is used to record patients who
accessed a service. There are 12,000+ rows in the database.

The database is designed in a such a way that patients have a seperate
record (row) for each month that they used the service. Each patients has a
unique PatientID number, however, many patients have more than one number and
I want to identify these patients, but do not know how and would apprecaite
your help in doing this.

The datbase is in the following format:

Month PatientID SurnameForename D.O.B Address etc......
Jun05 15678 BloggsJoe 23/4/07 Any Street
Apr05 15745 AnyOne 15/8/90 Whereever
Jan05 15896 BloggsJoe 23/4/07 Any Street

As you can see from the above example Joe Bloggs has two entries, one for
June and one for January but they have different PatientIDs (which they
should not). I would like to filter the 12,000 records or run a pivot to
identify patients which have multiple PatientIDs. Is there anyway that I can
do this?

Thank you in advance, Richard