• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

count function?

Ranjana

New Member
Hello everyone,
I'm working on a huge data set on excel. The data consists of patient names and the medications they are on. The medication gets renewed after certain period of time by physicians if needed and the renewed medications are listed twice / thrice (depending on number of times they are renewed).
I need to find out all the patients who have at least 5 or more medications renewed (that means appearing more than once). Can someone help me with this please?
My understanding is that if I can somehow use count function - something like...For each patient, count no. of repeat medications and then filter where the total count is 5 or more.
Here is what the data looks like:
Name Medication Name
John Med A
John Med A
John Med B
John Med C
John Med D
John Med D
John Med D
John is on 4 medications and out of that 2 are repeated more than once. So I'm interested in calculating this no. '2'. Once done I can do a filter.
Thanks and hoping to get the solution on this great forum.
R
 
Fastest way to do this would be to create 2 PivotTables. First one re-organizes our data into desired label, and the 2nd provide our answer. See attached for example.
 

Attachments

  • Count Data.xlsx
    15.1 KB · Views: 6
Hi Luke and Deepak,

Thanks so much for your response. I need little more help. Please see the screenshot attached. I want to arrange the data as three different columns how you have arranged it so that I can built the second pivot from it. Can you please provide me some instruction on how to do that?

Once again thanks for the solution.
R.
 

Attachments

  • Poly-pharmacy screenshot.PNG
    Poly-pharmacy screenshot.PNG
    115.2 KB · Views: 6
Back
Top