How to populate the misisng values in sequential order and checking the condition in other column

[Sri_devi]

i have raw data as below:

Subject_ID	Date	Time_administered	Dose_administered	__of_capsules_administered	Study_day_IP
001-001	4-Mar-21	45:00.0	25 mg	1	TP1-Day1
001-001	5-Mar-21	50:00.0	25 mg	1	TP1-Day2
001-001	6-Mar-21
001-001	7-Mar-21
001-001	8-Mar-21
001-001	9-Mar-21
001-001	10-Mar-21
001-001	11-Mar-21
001-001	12-Mar-21
001-001	13-Mar-21
001-001	14-Mar-21
001-001	15-Mar-21
001-001	16-Mar-21
001-001	17-Mar-21
001-001	18-Mar-21	45:00.0	50 mg	2	TP1-Day15
001-001	19-Mar-21
001-001	20-Mar-21
001-001	21-Mar-21
001-001	22-Mar-21
001-001	23-Mar-21
001-001	24-Mar-21
001-001	25-Mar-21	00:00.0	50 mg	2	TP1-Day22
001-001	26-Mar-21
001-001	27-Mar-21
001-001	28-Mar-21
001-001	29-Mar-21
001-001	30-Mar-21
001-001	31-Mar-21	30:00.0	50 mg	2	TP1-Day28
001-001	1-Apr-21	25:00.0	50 mg	2	TP1Washoutday29
001-001	2-Apr-21
001-001	3-Apr-21
001-001	4-Apr-21
001-001	5-Apr-21
001-001	6-Apr-21
001-001	7-Apr-21
001-001	8-Apr-21
001-001	9-Apr-21
001-001	10-Apr-21
001-001	11-Apr-21
001-001	12-Apr-21
001-001	13-Apr-21
001-001	14-Apr-21	00:00.0	25 mg	1	TP2-Day1
001-001	15-Apr-21
001-001	16-Apr-21
001-001	17-Apr-21
001-001	18-Apr-21
001-001	19-Apr-21
001-001	20-Apr-21
001-001	21-Apr-21
001-001	22-Apr-21	26:00.0	25 mg	1	TP2-Day8
001-001	23-Apr-21
001-001	24-Apr-21
001-001	25-Apr-21
001-001	26-Apr-21
001-001	27-Apr-21
001-001	28-Apr-21
001-001	29-Apr-21	15:00.0	50 mg	2	TP2-Day15
001-002	9-Mar-21	00:00.0	25 mg	1	TP1-Day1
001-002	10-Mar-21	40:00.0	25 mg	1	TP1-Day2
001-002	11-Mar-21
001-002	12-Mar-21
001-002	13-Mar-21
001-002	14-Mar-21
001-002	15-Mar-21
001-002	16-Mar-21	10:00.0	25 mg	1	TP1-Day8
001-002	17-Mar-21
001-002	18-Mar-21
001-002	19-Mar-21
001-002	20-Mar-21
001-002	21-Mar-21
001-002	22-Mar-21
001-002	23-Mar-21	00:00.0	50 mg	2	TP1-Day15
001-002	24-Mar-21
001-002	25-Mar-21
001-002	26-Mar-21
001-002	27-Mar-21
001-002	28-Mar-21
001-002	29-Mar-21
001-002	30-Mar-21	50:00.0	75 mg	3	TP1-Day22
001-002	31-Mar-21
001-002	1-Apr-21
001-002	2-Apr-21
001-002	3-Apr-21
001-002	4-Apr-21
001-002	5-Apr-21
001-002	6-Apr-21
001-002	20-Apr-21	30:00.0	25 mg	1	TP2-Day1
001-002	21-Apr-21	00:00.0	25 mg	1	TP2-Day2
001-002	22-Apr-21
001-002	23-Apr-21
001-002	24-Apr-21
001-002	25-Apr-21
001-002	26-Apr-21
001-002	27-Apr-21	00:00.0	25 mg	1	TP2-Day8
001-002	28-Apr-21
001-002	29-Apr-21
001-002	30-Apr-21
001-002	1-May-21
001-002	2-May-21
001-002	3-May-21
001-002	4-May-21
001-002	5-May-21	00:00.0	50 mg	2	TP2-Day15
001-002	6-May-21
001-002	7-May-21
001-002	8-May-21
001-002	9-May-21
001-002	10-May-21
001-002	11-May-21	03:00.0	75 mg	3	TP2-Day22
001-002	12-May-21
001-002	13-May-21
001-002	14-May-21
001-002	15-May-21
001-002	16-May-21
001-002	17-May-21
001-002	18-May-21	30:00.0	75 mg	3	TP2-Day28
001-002	19-May-21	45:00.0	75 mg	3	TP2Washoutday71
001-002	20-May-21
001-002	21-May-21
001-002	22-May-21
001-002	23-May-21
001-002	24-May-21
001-002	25-May-21
001-002	26-May-21
001-002	27-May-21
001-002	28-May-21
001-002	29-May-21
001-002	30-May-21
001-002	31-May-21

I want the data as below :

Subject_ID	Date	Time_administered	Dose_administered	__of_capsules_administered	Study_day_IP
001-001	4-Mar-21	45:00.0	25 mg	1	TP1-Day1
001-001	5-Mar-21	50:00.0	25 mg	1	TP1-Day2
001-001	6-Mar-21				TP1-Day3
001-001	7-Mar-21				TP1-Day4
001-001	8-Mar-21				TP1-Day5
001-001	9-Mar-21				TP1-Day6
001-001	10-Mar-21				TP1-Day7
001-001	11-Mar-21				TP1-Day8
001-001	12-Mar-21				TP1-Day9
001-001	13-Mar-21				TP1-Day10
001-001	14-Mar-21				TP1-Day11
001-001	15-Mar-21				TP1-Day12
001-001	16-Mar-21				TP1-Day13
001-001	17-Mar-21				TP1-Day14
001-001	18-Mar-21	45:00.0	50 mg	2	TP1-Day15
001-001	19-Mar-21				TP1-Day16
001-001	20-Mar-21				TP1-Day17
001-001	21-Mar-21				TP1-Day18
001-001	22-Mar-21				TP1-Day19
001-001	23-Mar-21				TP1-Day20
001-001	24-Mar-21				TP1-Day21
001-001	25-Mar-21	00:00.0	50 mg	2	TP1-Day22
001-001	26-Mar-21				TP1-Day23
001-001	27-Mar-21				TP1-Day24
001-001	28-Mar-21				TP1-Day25
001-001	29-Mar-21				TP1-Day26
001-001	30-Mar-21				TP1-Day27
001-001	31-Mar-21	30:00.0	50 mg	2	TP1-Day28
001-001	1-Apr-21	25:00.0	50 mg	2	TP1Washoutday29
001-001	2-Apr-21				TP1Washoutday30
001-001	3-Apr-21				TP1Washoutday31
001-001	4-Apr-21				TP1Washoutday32
001-001	5-Apr-21				TP1Washoutday33
001-001	6-Apr-21				TP1Washoutday34
001-001	7-Apr-21				TP1Washoutday35
001-001	8-Apr-21				TP1Washoutday36
001-001	9-Apr-21				TP1Washoutday37
001-001	10-Apr-21				TP1Washoutday38
001-001	11-Apr-21				TP1Washoutday39
001-001	12-Apr-21				TP1Washoutday40
001-001	13-Apr-21				TP1Washoutday41
001-001	14-Apr-21	00:00.0	25 mg	1	TP2-Day1
001-001	15-Apr-21				TP2-Day2
001-001	16-Apr-21				TP2-Day3
001-001	17-Apr-21				TP2-Day4
001-001	18-Apr-21				TP2-Day5
001-001	19-Apr-21				TP2-Day6
001-001	20-Apr-21				TP2-Day7
001-001	21-Apr-21
001-001	22-Apr-21	26:00.0	25 mg	1	TP2-Day8
001-001	23-Apr-21				TP2-Day9
001-001	24-Apr-21				TP2-Day10
001-001	25-Apr-21				TP2-Day11
001-001	26-Apr-21				TP2-Day12
001-001	27-Apr-21				TP2-Day13
001-001	28-Apr-21				TP2-Day14
001-001	29-Apr-21	15:00.0	50 mg	2	TP2-Day15
001-002	9-Mar-21	00:00.0	25 mg	1	TP1-Day1
001-002	10-Mar-21	40:00.0	25 mg	1	TP1-Day2
001-002	11-Mar-21				TP1-Day3
001-002	12-Mar-21				TP1-Day4
001-002	13-Mar-21				TP1-Day5
001-002	14-Mar-21				TP1-Day6
001-002	15-Mar-21				TP1-Day7
001-002	16-Mar-21	10:00.0	25 mg	1	TP1-Day8
001-002	17-Mar-21				TP1-Day9
001-002	18-Mar-21				TP1-Day10
001-002	19-Mar-21				TP1-Day11
001-002	20-Mar-21				TP1-Day12
001-002	21-Mar-21				TP1-Day13
001-002	22-Mar-21				TP1-Day14
001-002	23-Mar-21	00:00.0	50 mg	2	TP1-Day15
001-002	24-Mar-21				TP1-Day16
001-002	25-Mar-21				TP1-Day17
001-002	26-Mar-21				TP1-Day18
001-002	27-Mar-21				TP1-Day19
001-002	28-Mar-21				TP1-Day20
001-002	29-Mar-21				TP1-Day21
001-002	30-Mar-21	50:00.0	75 mg	3	TP1-Day22
001-002	31-Mar-21				TP1-Day23
001-002	1-Apr-21				TP1-Day24
001-002	2-Apr-21				TP1-Day25
001-002	3-Apr-21				TP1-Day26
001-002	4-Apr-21				TP1-Day27
001-002	5-Apr-21				TP1-Day28
001-002	6-Apr-21				TP1-Day29
001-002	20-Apr-21	30:00.0	25 mg	1	TP2-Day1
001-002	21-Apr-21	00:00.0	25 mg	1	TP2-Day2
001-002	22-Apr-21				TP2-Day3
001-002	23-Apr-21				TP2-Day4
001-002	24-Apr-21				TP2-Day5
001-002	25-Apr-21				TP2-Day6
001-002	26-Apr-21				TP2-Day7
001-002	27-Apr-21	00:00.0	25 mg	1	TP2-Day8
001-002	28-Apr-21				TP2-Day9
001-002	29-Apr-21				TP2-Day10
001-002	30-Apr-21				TP2-Day11
001-002	1-May-21				TP2-Day12
001-002	2-May-21				TP2-Day13
001-002	3-May-21				TP2-Day14
001-002	4-May-21
001-002	5-May-21	00:00.0	50 mg	2	TP2-Day15
001-002	6-May-21				TP2-Day16
001-002	7-May-21				TP2-Day17
001-002	8-May-21				TP2-Day18
001-002	9-May-21				TP2-Day19
001-002	10-May-21
001-002	11-May-21	03:00.0	75 mg	3	TP2-Day22
001-002	12-May-21				TP2-Day23
001-002	13-May-21				TP2-Day24
001-002	14-May-21				TP2-Day25
001-002	15-May-21				TP2-Day26
001-002	16-May-21				TP2-Day27
001-002	17-May-21
001-002	18-May-21	30:00.0	75 mg	3	TP2-Day28
001-002	19-May-21	45:00.0	75 mg	3	TP2Washoutday71
001-002	20-May-21				TP2Washoutday72
001-002	21-May-21				TP2Washoutday73
001-002	22-May-21				TP2Washoutday74
001-002	23-May-21				TP2Washoutday75
001-002	24-May-21				TP2Washoutday76
001-002	25-May-21				TP2Washoutday77
001-002	26-May-21				TP2Washoutday78
001-002	27-May-21				TP2Washoutday79
001-002	28-May-21				TP2Washoutday80
001-002	29-May-21				TP2Washoutday81
001-002	30-May-21				TP2Washoutday82
001-002	31-May-21				TP2Washoutday83

Note the for some subject, as per date it study_day_ip should be tp2day29 be due to some condition it occurred on the tpt2day28. in this case please leave the before one as blank. For example in above table for subject 001-001, for date 21 Apr 20221 the study_day_ip should be  tpt2day 8 but due to the some issue at site tpt2day8 occurred on 22 Apr 2021 instead 21 Apr2021, so for this i would like to have blank date or can even if it populates as TPT2day7 is also fine with me.

Please let me know this is possible programmatically.

Please let me know if you need any further information or if you are not clear about above points.

Really appreciate if anyone can be of any help for me with this regards.

[Shmuel · Accepted solution]

It is not clear what you need.

Please define the rules how to correct the data.
Example in this case is not enough.

And please be presize when you mention values to check, as 'TP2-Day8'

does not match 'tpt2day8'.

[Sri_devi]

Thanks and i am so sorry to confuse. I will re-post with clear instructions. thank you.