Is this doable with excel? Categorizing by pattern?

[Claudia04]

Hi all!
So many posts have greatly helped to filter out unnecessary data.

However, I'm finding it hard to do the following with my data

My final goal is to separate a patient list by 3 categories into different sheets:

Category 1 DRUG GROUP (TA2,TA5,TE3,TR15,TR20) changed to DRUG GROUP (WR)
Category 2 DRUG GROUP (WR) changed to DRUG GROUP (TA2,TA5,TE3,TR15,TR20)
Category 3 DRUG GROUP (TA2,TA5,TE3,TR15,TR20) changed to another within the same DRUG GROUP (TA2,TA5,TE3,TR15,TR20)

sample data file.png

See my attached file.
I am really not sure if it is doable with excel unless I do it manually.
Is this doable at all?

Any help is much appreciated.

[Claudia04]

Attached the sample file

[Melvosh]

Nevermind, you figured out how to upload

[Claudia04]

Thank you Melvosh!
Got lost in the middle but found my way

[Melvosh]

Will patients always be grouped together as shown in your example? Will the dates always be in ascending order?

[FlameRetired]

Also are we to understand that 'Sample'E14 is a typo? Is WF. Should it be WR?

[Claudia04]

Hi Melvosh!

Yes they will, I'm trying to see how each patient changed his drug categories over time between 2016-2018.
Got the list of prescriptions issued and they are always sorted out by 1. PT ID and 2. Date of prescription.





Claudia

[Claudia04]

To FlameRetired,

My bad, that is a typo

The correct one would be like the following

sample data file_typo.png

[Melvosh]

Two questions:
What determines the initial category?
Can a patient have more than one category, or is the category for a patient the same for all rows, based on just the latest change?

[Claudia04]

Hi Melvosh!
A patient can be in only one category.

The category is based on which drug group the PT is prescribed first and the changes that follow over time.
If a patient is prescribed WR first and later on TA5, the patient is in category 2.
"Category 2 DRUG GROUP (WR) changed to DRUG GROUP (TA2,TA5,TE3,TR15,TR20)"

Thanks for asking

[Bo_Ry]

Please try at F2 and drag down

=LOOKUP(2,1/(MMULT(N(ISNUMBER(SEARCH(INDEX($E$2:$E$99,N(IF(1,MATCH(1,INDEX(1/($A$2:$A$99=A2),),{0,1})))),MID(SUBSTITUTE($I$13:$I$15,">",REPT(" ",100)),{1,201},100)))),{1;1})=2),$H$13:$H$15)

[Claudia04]

Hello Bo!

That is one BIG formula but it works!

Will apply it to the master file and let you know!

You gave me hope!
Thank you so so much!

Claudia