need to identify individuals using different medications in the same class

[bubblesthrillingham]

hi everyone,
I've been tasked to try and find a way to identify patients that are using multiple drugs from the same drug class for greater than 60 days.
My report has about 1500 lines
i was able to identify them using a pivot table.....but there was a lot of manipulation involved and really it was quite ugly and time consuming. im wondering if theres an easier way someone might be able to identify.....or if someone can point me at least in a direction. im still pretty noobish with excel.
Screenshot_20180214-163131.jpg
thanks for any help you can provide

[mehmetcik]

Please post a sample workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[bubblesthrillingham]

Hopefully this works. Thanks for any help you can provide.

[kevinu]

I look forward to seeing how this is solved but I have a question. Do you need to see if a patient has "ever" had the same med group for 90 days or just "for" 90 days? If they had the same med group for 100 days 2 years ago do you want to see that?

[sandy666]

Maybe something like this without separated dates but with days
Done with PowerQuery (add-in for Ex2010 Pro Plus)

[FlameRetired]

This takes a different approach. I got the same drugs/same class as sandy's, but I took that you wanted a count of classes with duplicates but different meds. So the following interprets that sandy's as one.

Find a helper column E that determines if the dates are > 60 days ago. With TODAY() in F2 this in E2 and filled down.

Please Login or Register  to view this content.

Then with each unique student in G1:M1 this formula ... array entered ... in G2 and filled across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Patient Name	Drug Name	Drug Class - GPI4	Fill Date	> 60 days	Today is:	A	B	C	D	E	F	G
2	A	Amitriptyline	5820	12/22/17	0	2/14/2018	1	0	0	0	0	0	0
3	B	Fluoxetine	5816	12/12/17	1
4	C	Escitalopram	5816	11/20/17	1
5	A	Amitriptyline	5820	1/22/18	0
6	C	Buproprion	5830	12/18/17	0
7	D	Fluoxetine	5816	1/15/18	0
8	A	Doxepin	5820	1/22/18	0
9	B	Trazodone	5812	12/13/17	1
10	E	Sertraline	5816	11/14/17	1
11	F	Amitriptyline	5820	2/1/18	0
12	A	Doxepin	5820	11/21/17	1
13	C	Escitalopram	5816	12/17/17	0
14	G	Fluoxetine	5816	1/5/18	0
15	A	Doxepin	5820	12/22/17	0
16	A	Amitriptyline	5820	11/21/17	1

[bubblesthrillingham]

Hi Kevin,
The intent for this ask is to identify patients that need intervention. Therefore, someone who filled for a 100 day supply of the same med group 2 years ago isnt really relevant unless they continued to fill those meds Hope that answers your question.

[kevinu]

Bubbles, if thats the case I'm going to assume that the data only includes patients that are currently using meds. So any old med usage will not be on the list. Is that correct? If so then we only need to look at the current date and the first date for each drug - class.

Looks like Flame has a solution - does that work for you?

[bubblesthrillingham]

Flame, this is awesome!!! You're seriously brilliant, it's almost exactly what I'm looking for. My only issue is I currently have about 2000 unique patients. Would I be able to do the array vertically instead of horizontally across the spreadsheet to allow for easy filtering? The array changes the correct value when I drag it horizontally but not vertically. Thank you!!

[bubblesthrillingham]

Google is my friend I got it. Thank you again Flame for all your help, it's greatly appreciated!

[FlameRetired]

If I understand correctly you want output arranged like this:

	A	B	C	D	E	F	G	H
1	Patient Name	Drug Name	Drug Class - GPI4	Fill Date	> 60 days	Today is:
2	A	Amitriptyline	5820	12/22/17	0	2/15/2018	A	1
3	B	Fluoxetine	5816	12/12/17	1		B	0
4	C	Escitalopram	5816	11/20/17	1		C	0
5	A	Amitriptyline	5820	1/22/18	0		D	0
6	C	Buproprion	5830	12/18/17	0		E	0
7	D	Fluoxetine	5816	1/15/18	0		F	0
8	A	Doxepin	5820	1/22/18	0		G	0
9	B	Trazodone	5812	12/13/17	1
10	E	Sertraline	5816	11/14/17	1
11	F	Amitriptyline	5820	2/1/18	0
12	A	Doxepin	5820	11/21/17	1
13	C	Escitalopram	5816	12/17/17	0
14	G	Fluoxetine	5816	1/5/18	0
15	A	Doxepin	5820	12/22/17	0
16	A	Amitriptyline	5820	11/21/17	1

If that is correct then copy/paste transposed the previous column headers as row headers and use this formula (array entered) and fill down.

Please Login or Register  to view this content.

Is this what you meant?

[kevinu]

flame... can you help me understand what the formula does? Its awesome.

[FlameRetired]

I will do my best hopefully without posting a "wall" of text. It will take me some time to organize my thoughts.

While I am working on this I encourage you to research the FREQUENCY function. It is the heart of this formula and can be confusing. Unfortunately a thorough detailed explanation of the concept "bins" in the context of FREQUENCY is hard to come by. I once found only one by extensive "googling" a few years ago. I lost the link so I may have to construct one myself.

I also encourage familiarity with the Evaluate Formula feature of Excel as well as usage of the F9 function key if you aren't already. Any explanations I offer will make heavy use of F9.

[kevinu]

I never knew about f9, Thanks I'll check it out

[FlameRetired]

OK. Here's a start.

With the formula in question in the formula bar select just a component of that formula and hit the F9 function key. It this case you will see arrays of output "visible" to Excel but not to us. Those are passed on en masse to the next step which can be examined the same way. These will give you clues as to how the formula works. It doesn't just apply to array formulas either.

Evaluate formula does the same thing in step by step order.

These are excellent tools for self instruction. I recommend both of them whenever I can. In fact some contributors will post an Fx as their avatar. (Perhaps they are trying to tell us something. )

[kevinu]

Flame thanks that did help me a lot..

[FlameRetired]

Update:

That formula worked for 2/14/2018.

Upon closing up for the weekend (2/16/2018 the criteria changed) I noticed patient C had a duplicate of code 5816 (rows 4 and 13). However this is a duplicate of the same drug (Escitalopram). The 'Fill dates' are different but both greater than 60 days ago.

My formula counts that as a duplicate.

Should it?

If it does not please let me know. That needs a different formula.