How to check and edit cells one by one

Hi, I have to check every cell of a column and if i find the word “Commercial” I have to delete just the commercial word if i find the word “rpa” i have to delete the row. I am stuck an will appreciate some help.

Hi @Eylul_C

You can use the LINQ Expressions to check the each row in Specific column based on your conditions delete the rows with rpa and make empty if the row has Commercial.

Check the below steps,
→ Use the Read range workbook activity to read the excel and store in a datatable called dt_Input.
→ Then use the assign activity to write the below LINQ Expression to delete the rows which has rpa,

- Assign -> dt_Output = dt_Input.AsEnumerable.Where(Function(row) Not(row("Column name").ToString.Equals("rpa"))).CopyToDataTable()

→ Then use another assign activity to write the below LINQ Expression to update the row with null value if the row contains Commercial,

- Assign -> dt_Output = (From row In dt_Output.AsEnumerable()
                        Let ChangedValue As String = If(row("Column name").toString.equals("Commercial"), "", row("Column name").ToString)
                        Let newRow = dt_Output.Clone().Rows.Add(row.ItemArray.Select(Function(column) If(column.Equals(row("Column name")), ChangedValue, column)).ToArray())
                        Select newRow
                             	).CopyToDataTable()

→ Then use the write range workbook activiy to write the dt_Output to the excel.

Hope it helps!!

I made a workflow for your reference with Sample data… @Eylul_C

Check the below workflow,
Sequence3.xaml (12.3 KB)

Check the below file Sheet1 is Input and Sheet2 is Output,
expectedoutput.xlsx (10.2 KB)

Hope you understand!!

Hi thank you so much for helping

It’s my pleasure… @Eylul_C

I hope you find the solution for your query, Make my post Mark as Solution to close the loop.

Happy Automation!!

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