2

Okay, a colleague is trying to compile some dosage information. He has a spreadsheet containing Dates of Service, the patient's Medical Record #, and the name of the Medication. So, if Oxycodone appears 3 times on 9/30/15 for Medical Record 012345678 then what he would like to automatically receive is a report that looks like:

Serv Date...... MedRec#.......... Rx................. Doses

9/30/2015...... 012345678...... Oxycodone...... 3

9/29/2015......0123455567...... Aspirin............ 1

9/29/2015......0000345678...... Rantidine...... 2

Of course he has a huge number of dates, patients, and various meds for each patient on various days, so the above would cover many months for patients on various meds. I tried CountIF, and initially a Pivot Table looked promising but I'm lacking the skill and its driving me bats on how to automatically return a count for so many conditions being met and pulling the Rx name, Dates of Service, and Med Rec # out of the sheet and producing the report on a new sheet. Any ideas?

  • so you have "number of doses" in your raw data table? Or does one row implicitly indicate 1 dose? – Jon Oct 14 '15 at 2:53
1

Assuming that your data looks like this :

enter image description here

Add a column on the left, which will be a composite index based on Date, medrec# and Rx (I use a semicolon as a seperator) : =CONCATENATE(B2,";",C2,";",D2)

enter image description here

Now all you need to do is use COUNTIF to count the frequency of that INDEX (which would equal the number of Doses) : =COUNTIF(A:A,A2)

enter image description here

Then you can use Excel's Advance Filters to show only unique values :

enter image description here

Which will give you this filtered table :

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.