Excel Cell Editing Based on Value

Hello, I am facing problem while Filtering the Excel Sheet based on value.
1.In Excel Sheet, Check the cell value in the row whether the value “A” is there or Not
2.If the cell value A is there keep the value, else make empty on the cell.

Input Image:

Output Image:

Excel File:
MainFile (2).xlsx (11.7 KB)

What is the problem you are facing ? Can you provide screen shot of error and code which is generating this error ?

Hi @skini76,

I don’t know how to keep cell Value “A” in all the row from the Excel file. Can you please tell me the way based on this conditions:
1.In Excel Sheet, Check the cell value in the row whether the value “A” is there or Not
2.If the cell value A is there keep the value, else make empty on the cell.

@Arunachalam

Let us take you are storing your excel sheet in dta by using Read Range activity

    Array Columns Names= (From p In dta.Columns.Cast(Of System.Data.DataColumn)
                                           Select  Convert.ToString(p.ColumnName)).ToArray

Let us take one more datatable dtb= dta.DefaultView.ToTable(False, Names.Skip(4))
Iam skipping first 4 columns because you dont want to change any thing in the first 4 that’s y.

List of Indexes of Headers

List ABC = (From p In dta.Select
                  where p(4).ToString.ToLower.Equals("name")
                 Select Convert.ToString(dta.Rows.Index( p))).ToList

Create one more DataTable dtc= dtb.Clone()

Cretae one more DataTable dtd= dta.DefaultView.ToTable(False, Names.Take(4))

Now Run ForEachRow for dtb
Inside ForeachRow Add one If Condition
Add condition like this
not ListABC.Contains(dtb.Rows.Index(row).ToString)
In If Part follow the below procedure.

Create one more List

  List DEF = (From p In row.ItemArray
                 select If(p.ToString.ToLower.Equals(a),"A",String.Empty)).ToList

Now use Add DataRow Activity fill DataTable property as dtc and Values as DEF.ToArray

At Last Merge Merge dtc to dtd and write in Excel sheet.

Note: I didn’t tested the code. Please resolve if it throws any exception. And follow the logic

Regards,
Mahesh

Thanks @MAHESH1 , Can you please share me the “xaml” File for this.

MainFile (2).xlsx (11.7 KB)

@MAHESH1

Can you please check this image, I am getting error while assign the value of DataTable dtb.
DataTableAssignError

Thanks,
Arunachalam

Arun, It looks like you are not very familiar with accessing Excel in UiPath, my strong recommendation is to go though the tutorials once, most of your doubts/issues will be cleared. If you keep raising basic questions about the usage, the Q & A will go on for a long period, it will take lot of time for you to develop the workflow. You should use this forum only for unmanageable exceptions which is not covered by any tutorials or no help available in the forum. Sorry if you have felt bad after reading my comments, but this is in your interest.

Hi @Arunachalam

PFA xaml

CellEditing.zip (14.5 KB)

Regards,
Mahesh

@MAHESH1

Thanks for response. Yesterday I got answer based on your given logic.