Excel 2 column Matching

Nature of Desc OD/TP Remarks
Not At Fault Payment Garage Failed
At Fault Payment Garage
At Fault Payment Garage
Not At Fault Payment Garage Failed

Hello guys i have an excel with column like this.

i want to write failed in Remarks column only for column that matches “Not At Fault & Payment Garage”.
And leave the other Remark column blank.
So does anyone have the solution for this.

Hi @Gokul_Murali

You can use this query in Invoke Code:

io_dt.AsEnumerable.ToList.ForEach(Sub(row)
row(“Remarks”) = If((row("Nature Of Desc").ToString.Equals("Not At Fault") And row("OD/TP").ToString.Equals("Payment Garage")),"Failed","")
End Sub)

Edit: Attaching Input, Output & Workflow

Input:

image

Output:

image

Workflow:

image

Hope this helps,
Best Regards.

Hi @Gokul_Murali

Try this expression in the Invoke code activity

dt.AsEnumerable.ToList.ForEach(Sub(row)
row(“Remarks”) = (if(row("Nature of Desc").ToString.Equals("Not At Fault") And row("OD/TP").ToString.Conatins("Payment Garage”)),"Failed","")
End Sub)

Regards
Gokul

Hi @Gokul_Murali

Give a try to this.

Hope this helps.

@Gokul001

Showing Error

@Suraj_B_Shetty

Hello i have tried but when i use assign activity it is not working and i replaced it with write cell its working. but taking a lot of time.

Hi @Gokul_Murali

Sorry Try with this Expression in the Code

dt.AsEnumerable.ToList.ForEach(Sub(row)
row("Remarks") = If(row("Nature of Desc").ToString.Equals("Not At Fault") And row("OD/TP").ToString.Equals("Payment Garage"),"Failed","")
End Sub)

You need to declare the Data Table in the Argument

Regards
Gokul

@Gokul_Murali

Please find the attached xaml:
Test234.xaml (7.2 KB)

Hope this helps,
Best Regards.

Check below workflow

(From d In  DT.AsEnumerable
Let gp=If(d(0).ToString.equals("Not At Fault") And d(1).ToString.equals("Payment Garage") ,"Failed",String.empty)
Select  DT1.Rows.Add(New Object(){d(0),d(1),gp})).copyToDataTable

SampleProcess.zip (3.0 KB)

Input

Input

Output

output

1 Like

@Gokul001

For me no error in the Invoke code

Check out the above expression @Gokul_Murali

@Gokul001
Bro after invoke code i used write range.

it is over writing the previous Remarks update.

is there any solution for that

You can write the output in the new sheet @Gokul_Murali

@Gokul001

when i try to over write it in a new sheet.

It is removing the processed Remark row

Hi @Gokul_Murali ,
Try this in invoke code and make the direction of data table as in/out:

dt.AsEnumerable.ToList.ForEach(Sub(row)
row(“Remarks”) = If(row(“NATURE OF LOSS DESC”).ToString.Equals(“Not At Fault”) And row(" OD / TP ").ToString.Equals(“PAYMENT –GARAGE OD ( NON-AGENCY )”),“Check the input”,row(“Remarks”).tostring)
End Sub)

You can try with Append Rnage activity, If the file is exit in the folder

Regards