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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top