ID start_date new_end_Date drug drug_1 drug_2 Need x1 36526 36708 t t 1 x1 39448 40086 l l 2 x1 40087 40421 a,l a l 3 x1 40422 41890 a a 3 x1 41891 42453 l l 3 x1 42454 43698 c,l c l 4 x1 43699 44026 l l 4 x1 44027 44304 l,r l r 5 x1 44305 44474 r r 5   Hello Guys,   I need to create a column if there is true change of drug(Column 'need'). Drug_1 and drug_2 is nothing but 'drug' separated into different columns. What is needed (NEED Column above): 1) first row - the drug is t and first drug so column need should have value of 1 2) Second row - the drug is l (so drug changed from t to l) - column value should be 2   3) third row - the drug is a,l (even though drug l from second row exists here but new drug 'a' is added) - column value should be 3 4) Fourth row - drug is 'a' (previous row drug 'a' exists is here, not a true change of drug) - column value should be still 3. 5) Fifth row - drug is l (fourth row drug 'a' doesn't exists is here, but third row have a,l (a,l changed to a and then to l so continued)  -  not a true change of drug) - column value should be still 3.   6) sixth row - drug is c,l  (3,4,5 rows have  a,l & a & l -  but c is added) - column value should be 4. 7) seventh row - drug is l  (6 row had c,l  and this row had l so no change) - column value should be 4.      eighth row - drug is l,r  (6 row had c,l and 7row had l - but r is added here ) - column value should be 5 9) ninth row - drug is r  (8 row had l,r and this row had r so no change ) - column value should be 5   I know this is tricky, sorry if my question is not clear. Appreciate any help. Thank you All. ... View more