Matching Data

docterryt

New Member
Joined
Sep 26, 2006
Messages
8
I have a spreadsheet with one column lists the Physicians' names, while other columns lists their patients' names and the date the patient was seen. My problem........I have to count how many appts each doctor had seen during a certain quarter of the year. I may have one doctor having 250 yearly individual patient appts involving 35 patients but only 75 individual appts patients involving 15 patients in a given quarter. How do I find each Doctor's name and match his name to his patients during a specific quarter of the year.

Ultimately, I need a way to count how many individual patients appts and the number of patients seen during a specific period of time by referencing 3 columns of data.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello docterryt, welcome to the board.
This can be done with formulas (providing I fully understand your needs) but the best
way to go about it would depend on your data layout.
Are all the doctors/patients/appmnts for the entire year all on a single sheet?
Does each doctor have their own sheet for the year?
Are the sheets perhaps already broken up by quarters?

Myself, I'd be able to do it easier using vba but I know it can be (and is probably best)
done with formulas.
In either case I'd need to have an idea of what your worksheets look like in order to
come up with a solution tailored to your needs.
 

docterryt

New Member
Joined
Sep 26, 2006
Messages
8
All the doctors/patients/appts are on the same sheet in 3 separate columns. The data is entered in chronological order as the appts occur, e.g.,

Given the following sheet data:

Doctor Patient Appt Date
Smith Jones 1/1/2006
Smith Jones 1/15/2006
Davis James 1/22/2006
Toms Dickson 1/28/2006

I would like to produce the following sheet data:

# Patients
Doctor Quarter 1 Quarter 2 Quarter 3
Smith 2
Davis 1
Toms 1

Thanks
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

Or, to save putting the dates into the formulae you could use :
Book1
ABCDEFGHI
1DoctorPatientApptDateDoctorQ1Q2Q3Q4
2SmithJones1/1/06From:1/1/061/4/061/7/061/10/06
3SmithJones15/1/06To:31/3/0630/6/0630/9/0631/12/06
4DavisJames22/1/06Smith2100
5TomsDickson28/1/06Davis1000
6SmithFred5/4/06Toms1000
Sheet2


Formula in F4, copied across and down is : =SUMPRODUCT(--($A$2:$A$10=$E4),--($C$2:$C$10>=F$2),--($C$2:$C$10<=F$3))
Adjust ranges to suit your data.
 

docterryt

New Member
Joined
Sep 26, 2006
Messages
8

ADVERTISEMENT

This great! Thanks a lot.

Terry
 

docterryt

New Member
Joined
Sep 26, 2006
Messages
8
One last question. How do I just count the number of doctors and patience seen during the quarters.

For example, from the data above I need to show:

3 doctors saw 4 patients during quarter 1
1 doctor saw 1 patient during quarter 2, etc...

I need to do this without providing a cell that references the specific doctor like $A$2:$A$10=$E4 where $E4 is a specific doctors name. I just want to somehow count the number of doctors that saw patients in column A which is 3, however, during quarter 1, all 3 doctors saw 4 patients and during quarter 2, 1 doctor saw 1 patient, and during quarters 3 and 4, 0 doctors saw 0 patients.

The spreadsheet should look something like this.

Quarter 1
# of Doctors # of Patients
3 4

Quarter 2
# of Doctors # of Patients
1 1

Quarter 3
# of Doctors # of Patients
0 0

Quarter 4
# of Doctors # of Patients
0 0


Thanks

Terry
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Can you please clarify first how you want to deal with duplicates. In your original example Dr Smith saw Patient Jones twice in January 2006. Does this count as two doctors and two patients or what? Do you only have one Dr. Smith. Also was Patient Jones the same person twice or was it Ellie Jones on 1/1/06 and Peter Jones on 15/1/06?
 

docterryt

New Member
Joined
Sep 26, 2006
Messages
8
I want duplicated doctors in a quarter to be counted as 1 doctor. So Dr. Smith would count as 1 doctor seeing 2 patients (Jones twice) in January
or during the first quarter.

Thanks,

Terry
 

Watch MrExcel Video

Forum statistics

Threads
1,108,612
Messages
5,523,891
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top