Excel Manipulations, The result of final status column should come as OK & NOT OK

Hi, In this Excel sheet
The result of final status column should come as OK & NOT OK

For OK - Charge columns should only contains CASH, Other than CASH any other text is there on the charge columns it should come as NOT OK.

Attached the Excel sheet.


Test.xlsx (9.0 KB)

Hi @Praveen_Vs ,

Try oin this format
ExcelManipulation.xaml (16.8 KB)
1.Read the excel Data
2.Assign charge1,charge2,charge3,charge4,charge5 as a variable
3.use if condition to print the Final status.

it shows like invalid xaml file while opening

Hi @Vicky_K
Please find attached workflow that is fulfilling all conditions.

OkNotOk_Flow.zip (10.0 KB)

Workflow contain-
1.Read range
2. Assign columns.
3. second Assign combine all the columns into one
4. If activity to find out final result.

Let me know in case of any concern.
Thanks

1 Like

Hi @Praveen_Vs Use below query

(From d In DT.AsEnumerable
Let gp=If(d(6).ToString.equals("CASH"),"OK","Not OK")
Select  DT1.Rows.Add(New Object(){d(0),d(1),d(2),d(3),d(4),d(5),d(6),gp})).copyToDataTable
  • DT is the data table from Read Range
  • DT1 is the cloned data of DT

Check below workflow

SampleProcess.zip (11.4 KB)

Input

Capture

Output

Capture1

1 Like

For Henry & Sam the result needs to be come as Ok, I think its considering the empty cells & giving the result as NOT OK, Can we able to make it as OK

@Praveen_Vs There are five Charge columns in the table. May I know based on which charge column you want to make this logic. Currently, the above logic is built based on column Charge5

1 Like

@Praveen_Vs - Ok I got your requirement. If all the Charge columns equals to Cash the final column status should set to OK

Below the updated query

(From d In DT.AsEnumerable
Let gp=If(d(2).ToString.equals("CASH") And d(3).ToString.equals("CASH") And d(4).ToString.equals("CASH") And d(5).ToString.equals("CASH") And d(6).ToString.equals("CASH"), "OK","Not OK")
Select  DT1.Rows.Add(New Object(){d(0),d(1),d(2),d(3),d(4),d(5),d(6),gp})).copyToDataTable

Updated Workflow

SampleProcess.zip (11.5 KB)

Updated Output

Capture

1 Like

Yes All the charge columns contains CASH, it means OK
Also, Only 1 or 2 charge charge columns contains CASH and remaining charge columns contains empty cell means, It needs to be come as OK.

@ushu
The output i want to get like
Henry, Harry & Sam final status need to come as OK only.

@Praveen_Vs Check updated workflow below

SampleProcess.zip (11.5 KB)

Output

Capture

1 Like

Hi @Praveen_Vs
Conditions.xaml (21.9 KB)
Can you try this xaml and let me know if any clarification
here i use multiple if con.dition this is working fine i think you can check it in your end also.

Happy Automation!!!
Thanks and Regards,
Vickiramathithyan K

Hi
Please follow below steps to
1.read range using read range from workbook file activity
2.for each row
3.in for each row use one if condition “String.Join(” “,{CurrentRow(“Charge1”),CurrentRow(“Charge2”),CurrentRow(“Charge3”),CurrentRow(“Charge4”)}).Trim.EndsWith(“CASH”)”
4 in then section assign activity CurrentRow(“Final Status”) = OK
in else section assign activity CurrentRow(“Final Status”) = NOTOK
5. write range activity in workbook file
Regards

1 Like