I work in medical billing and need to consolidate patient names to run reports for my supervisor. Here's the issue:
I receive a detailed report from an outside agency that lists all pharmacies, physicians, patients, and clinics for the previous month. The latest report had over 12,000 rows. Every row represents a medication that was prescribed to a patient. So multiple rows might have information on the same patient. To demonstrate I created the following example:
So John Doe has two records because he has been prescribed two different types of medication. I want to create a report that tells me how many individual patients I have at a particular clinic location. I can create a pivot table that tells me the "count" but as it does not take into account that someone with the same first name, last name, and birthdate are a single individual – my result is way off. I am hoping there is a way for me to consolidate all the entries for a single patient into one group, so individuals are not double or triple counted.
Best Answer
This is what I would do in your situation:
concat
to achieve thisconcat
Play with the pivot table until you get what you want.