Hi, i am looking to create additional 3 columns from the input table below to give the earliest grade change if an ID falls into group 2. For example for ID = a1, it was classified as Group 2 on 1 Feb 2015 and the earliest change in grade happens on 1 Apr 2015 when it changes to 4. I would also like to calculate the number of months it took to change from grade 3 to grade 4. i have also attached the desired output table below. Thanks! Input table ID Date Grade Group a1 1 jan 2015 3 1 a1 1 feb 2015 3 2 a1 1 mar 2015 3 1 a1 1 apr 2015 4 1 b1 1 jan 2015 5 2 b1 1 feb 2015 5 1 b1 1 mar 2015 5 1 c1 1 jan 2015 5 1 c1 1 feb 2015 5 1 c1 1 mar 2015 5 1 c1 1 apr 2015 5 1 c1 1 jan 2018 6 2 c1 1 feb 2018 7 1 d1 1 jan 2015 4 2 d1 1 jan 2016 5 1 d1 1 feb 2016 5 2 d1 1 mar 2016 6 1 e1 1 jan 2015 7 2 e1 1 jan 2017 8 1 Output table ID Date Grade Group New grade Date of new grade No. of mths to get to new grade a1 1 jan 2015 3 1 a1 1 feb 2015 3 2 4 1 apr 2015 2 a1 1 mar 2015 3 1 a1 1 apr 2015 4 1 b1 1 jan 2015 5 2 b1 1 feb 2015 5 1 b1 1 mar 2015 5 1 c1 1 jan 2015 5 1 6 1 jan 2018 36 c1 1 feb 2015 5 1 c1 1 mar 2015 5 1 c1 1 apr 2015 5 1 c1 1 jan 2018 6 2 7 1 feb 2018 1 c1 1 feb 2018 7 1 d1 1 jan 2015 4 2 5 1 jan 2016 12 d1 1 jan 2016 5 1 d1 1 feb 2016 4 2 6 1 mar 2016 1 d1 1 mar 2016 6 1 e1 1 jan 2015 7 2 8 1 jan 2017 24 e1 1 jan 2017 8 1
... View more