How to compare data e correct sufix based on some conditions

[wcamarg3]

Hi all,

I hope anyone can help me.

I have a table with serial numbers, date, status and OP.

When there is a "NOK" status it places a suffix "1" on the OP, and I would like to correct it.

So, every time there is a "NOK" it should compare it with the "Checkpoint" Status, find the same Operation "prefix", "middle" and correct the "sufix". 

Below an example of what I want:

SN	Date & Time	Status	OP
X12	9/29/2021 21:47	Complete	A_110_4
X23	9/29/2021 21:03	Complete	A_110_4
X23	9/29/2021 21:02	Complete	A_110_4
X23	9/29/2021 21:00	Complete	A_110_4
X33	9/29/2021 10:50	Checkpoint	A_120_3
X33	9/29/2021 11:48	NOK	A_120_1
X33	9/29/2021 11:53	OK	A_120_3
X45	9/29/2021 11:53	Complete	B_10_4
X45	9/29/2021 11:48	Complete	B_10_4
X45	9/29/2021 10:00	Complete	B_10_4

According to "Checkpoint" the "NOK" Status should be "A_120_1".

Below the desired table:

SN	Date & Time	Status	OP
X12	9/29/2021 21:47	Complete	A_110_4
X23	9/29/2021 21:03	Complete	A_110_4
X23	9/29/2021 21:02	Complete	A_110_4
X23	9/29/2021 21:00	Complete	A_110_4
X33	9/29/2021 10:50	Checkpoint	A_120_3
X33	9/29/2021 11:48	NOK	A_120_3
X33	9/29/2021 11:53	OK	A_120_3
X45	9/29/2021 11:53	Complete	B_10_4
X45	9/29/2021 11:48	Complete	B_10_4
X45	9/29/2021 10:00	Complete	B_10_4

Thank you,

Wil

[PhilipMannering]

Hi @wcamarg3 

Something like this?

[wcamarg3]

Hi @PhilipMannering . I will not work in all situations.

If I have another "Checkpoint" or another "NOK" it will not work. 

It should take in consideration the "SN".

[shreyanshrathod]

@wcamarg3 

See if this helps.

Regards,

Shreyansh

[wcamarg3]

Hi @shreyanshrathod . Unfortunately no.

I did the tryout. It doesn't work as desired. For example I put the checkpoint for OP A_102_1.