Filter data and remove the rows


One Excel sheet some set of data’s are there like (dhiebdj-FS,hdjebfjsbsj-FS,hdidhdudh-RE,hdudhdjsh-RE,hdudhdu,dhdudhu-R,hdudhfjej-F). we want to remove the whole rows are endwith (-FS,-RE)
In the sheet 3 column is there In A column has (dhiebdj-FS,hdjebfjsbsj-FS,hdidhdudh-RE,hdudhdjsh-RE,hdudhdu,dhdudhu-R,hdudhfjej-F)
B column has ( fshdh,heidv,bdjdb,bdjeb,dbhrhd,rbdjdj,dbjdhd,)
C column has ( se,bdjd,ndej,ee,rhrj,bejd,dr,df)

HI @Aarthy1

Can you share the excel files with input and output?


Hi @Aarthy1 ,

Can you try this



OutputDT = inputDT.AsEnumerable.Where(Function(DR) Not string.Join(“,”,DR.ItemArray.ToArray).Contains(“-FS”) AND NOT string.Join(“,”,DR.ItemArray.ToArray).Contains(“-RE”)).CopyToDataTable()

Your output will be


FilterDataAndRemove.xaml (7.4 KB)

But , in orginal Excel i have many columns upto BT

This is the file , please check and inform me the correct answer
In the Excel file i mension the requirement also

file.xlsx (10 KB)

Hi @Aarthy1
Read the excel and store it in datatable and inside for each row in datatable use if condition currentrow (0). tostring.endswith(“-FS”) or currentrow(0).tostring.endswith(“-RE”) then use remove datarow activity.after wise write range activity to write the filtered datatable
Use filter datatable activity and give the if condition inside and try

I tried but i facing the error


You want to check only one column or all the columns?

If only one column then use filter datatabe

If multiple columns…then follow the steps

  1. Read data in datatable dt.
  2. For each (not for each row in datatable)… give ‘in’ argument as Enumerable.Range(0,dt.Columns.Count-1).ToArray and change ‘type’ argument in properties to int32
  3. Inside loop use filter datatable with input and output datatable both as dt and in filter condition give currentitem endswith "your string" and select the mode as remove or select does not end with and select mode as keep anything is same

Hope this helps


While using filter data table the formula in the table is gone

Clone the datatable and use add datarow activity to add the row in cloned datatable in else block


You have formulae in excel? If yea then use for each row in excel and then check each row in if condition and use delete row…