How do I compare two cells and add remark?

Help Activities
, ,
1

Hey all,

I have an Excel file with values like in the screenshot attached below.

image

If a row in the column “ERP Code” is blank, I need to add remark as “XK01” in the column “Remarks”. Else if there’s value in a row, add remark as “XK02”

“Required Remarks” is added for your understanding.

How do I achieve this ?

Kindly attach an xaml file for my better understanding.

Thank You !

2

Hi @uio

→ Use the Read range workbook activity to read the excel and store in a datatable called DT.
→ Take an assign and write the below expression,

- Assign -> Output_DT = (From row In DT.AsEnumerable()
                         Let RequiredRemarks = If(String.IsNullOrEmpty(row.Field(Of String)("ERP Code")), "XK01", "XK02")
                         Select DT.Clone().Rows.Add({row(0), row(1), row(2), RequiredRemarks})
                                   ).CopyToDataTable()

→ Then use the write range workbook activity to write the Output_DT to same excel.

Check the below workflow for better understanding,

Sequence1
Sequence1847×580 34.7 KB

Hope it helps!!

1 Like
3

Since two different flow will continue based on the remarks, I’m preferring to use Excel write cell.

4

Hi @uio, Can you please share the input excel file?

5

Okay @uio

You want to use the excel activities, right. Then try the below workflow,
→ Use the Excel Process scope activity, inside of it insert the Use excel file activity.
→ Give the Path of the excel file in the Use excel file activity.
→ Inside Use excel file activity insert the For each excel row activity. Output is CurrentRow.
→ Inside for each insert the If activity and give the below condition,

String.IsNullOrEmpty(CurrentRow("ERP Code")) AndAlso String.IsNullOrWhiteSpace(CurrentRow("ERP Code"))

→ Inside Then block insert the Write cell activity and give the “XK01” in what to write field and give CurrentRow.ByField(“Required Remarks”) in where to write field.
→ Inside else block insert the Write cell activity and give the “XK02” in what to write field and give CurrentRow.ByField(“Required Remarks”) in where to write field.

Check the below workflow for better understanding,

Sequence1
Sequence1900×1703 124 KB

Hope it helps!!

6

Can you please share the project file ?

7

Okay @uio

Check the below workflow,
Sequence1.xaml (16.0 KB)

Change the Path of the file in Use excel file activity and change the sheet name in the For each excel row activity.

If you find the solution Mark my post Mark as solution to close the loop.

Hope it helps!!

8

Thanks a lot. I didn’t know methods like this exist.

9

Is there any alternate for CurrentRow.ByField(“Remarks”) ? Because I get an error stating “ByField is not a member of System.Data.DataRow”. I’m using an older version of studio. Couldn’t update it because of policy issues.

10

Could you share your workflow… @uio

11

@uio

Please check with it if you are using workbook activities

Use the Read Range Workbook activity to read the Excel file and store the data in a DataTable variable.

Use the For Each Row activity to loop through each row in the DataTable.
In for Each Row in Data Table properties create a variable (Ex: CurrentRowIndex) in Index

Inside For Each Row in Data Table
Use If activity to check if the “ERP Code” column is empty for the current row

Provide the following Expression
String.IsNullOrEmpty(CurrentRow(“ERP Code”).ToString)

In then
Add Write Cell activity
In cell: “C”+(CurrrentrowIndex+2).tostring
Cell Content: “XK01”

Else
Add Write Cell activity
In cell: “C”+(CurrrentrowIndex+2).tostring
Cell Content: “XK02”

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.