Matching Data

docterryt

New Member
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hello docterryt, welcome to the board.
This can be done with formulas (providing I fully understand your needs) but the best
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.

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

Like this?
Book1
EFGHI
1DoctorQ1Q2Q3Q4
2Smith2100
3Davis1
4Toms1
Sheet2

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))

Sweet!

This great! Thanks a lot.

Terry

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

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?

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

Replies
3
Views
150
Replies
2
Views
748
Replies
3
Views
170
Replies
5
Views
519
Replies
4
Views
672

1,219,792
Messages
6,150,288
Members
450,949
Latest member
faizanmalik10

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.

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

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