How to input special remark base on row number in excel?

[yangyq10 (IvanY)]

Dear Experts

I have a request as below:

First, I need to filter rows with “X” in column [Complete Ship]

Secondly, for each [SO] in the selected rows,

• If there is only one row in [SO] (e.g. G000118520), the write “X1” in [Complete Remark]
• If there are more than one row in [SO] (e.g. G000000274 - 4 rows; G000000221 - 2 rows; G000000227 - 3 rows), then write “X2” in [Complete Remark]
  
  

May I know how to make it happen?

Test.xlsx (9.1 KB)

Thanks
Ivan

[tolga.kamci (Tolga Kamçı)]

Hi @yangyq10

You can use this workflow to do this.

Cheers.

Test.xaml (12.9 KB)

[Umadevi_Sanjeevi]

Hi @yangyq10 ,

Could you try below solution.

Step1: Read Excel as Datatable (DT)
Step2: Use Invoke Code activity

DT.AsEnumerable().ToList().ForEach(Sub(row)
If dt.AsEnumerable().Where(Function(x) x(“SO”).ToString.Equals(row(“SO”).ToString)).CopyToDataTable.Rows.Count>1 Then
row(“Complete Remark”)=“X2”
Else
row(“Complete Remark”)=“X1”
End If
End Sub)

You will get your expected output.

I attached Test.xaml for your reference.

Test.xaml (7.7 KB)

Thanks!

[yangyq10 (IvanY)]

@tolga.kamci Thank you for the suggestion. I try it just not but doesn’t work out

[yangyq10 (IvanY)]

@Umadevi_Sanjeevi Somehow I these 2 activities can’t be shown in my UiPath. Can you help screenshot more detail to me? Thank you in advance

[dkollyns (Diógenes Kollyns)]

Hi @yangyq10, you may try this.

After you got all data that you need apply this sequence below:
1.Filter Data Table: Filter dataTable by [Complete Ship] = X and output to filteredDataTable.
2.For Each Row: Loop through filteredDataTable and for each row:
• Filter Data Table (inside the loop): Filter filteredDataTable where [SO] = row(SO).ToString and store in soDataTable.
• If Condition: soDataTable.Rows.Count = 1
• Then: Assign X1 to row(Complete Remark)
• Else: Assign “X2” to row(“Complete Remark”)

5. Write Range: Write the modified filteredDataTable back to Excel.

[Umadevi_Sanjeevi]

Hi @yangyq10 ,

Can you check UiPath.excel.activities package version.

Steps Used in Test.Xaml

Step1: Use Read Range Activity to read your excel file. The output of the read range will be DT

Step2: Invoke code which already in test.xaml

Step3: Use Write Range activity to write your output in your local.

Thanks!

[tolga.kamci (Tolga Kamçı)]

@yangyq10 You must change the file paths on Workbook activities. If you have done it, what is the error then?

[yangyq10 (IvanY)]

@tolga.kamci

I figure it out
The reason I failed at the beginning is that I simply put Assign to “X1” in Else

But your sample show me need to put another if in Else for “X1”

Thank you