Match and Remove

I have a dataset

is there way to remove rows where Combined start date equals to TRS Combined start date? I want to avoid using each row loop as the dataset might have more than 100k rows

Any help would be greatly appreciated

Hi @ravindar.singh ,

  1. Read the excel data with Read range activity, which will give you the output the DataTable variable(say input_DT)
  2. Loop through the DT using For each row in datatable activity
  3. Inside the loop use a If condition to check if currentrow(“conditional start date”).Tostring = currentrow(“TRS conditional start date”).Tostring the inside if use Remove data row activity and pass currentrow as input which will remove that row


@Rajeswari24 thanks for your response. I was trying to find a different way as I don’t want to loop though each row because I might have more than 100k rows. I was trying to find a simpler way so I can avoid each loop activity.

@ravindar.singh can you upload the excel here


Book1.xlsx (8.8 KB)


1 Like

do we need to remove this row?

combined start date is blank

Book1.xlsx (8.9 KB)

With SQL I can cast data in empty row’s

Hi @ravindar.singh ,

In that case you can use the below LINQ query to filter the DT first

DataTable.AsEnumerable.Where(Function (x) x(“conditional start date”).tostring = x(“DRS conditional start date”).tostring).copyToDatatable

This will return you a new datatable. Now with the master datatable and matched rows datatable you can fetch only unmatched rows from master datatable using below LINQ query

dt_NonMatched_Data = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(functionr.Field(Of String)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of String)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()


1 Like

try this, it doesnt use for each activity @ravindar.singh
test.xaml (7.9 KB)


  1. create list of unique trs combined start date
    dt.AsEnumerable.where(Function(row) Not String.IsNullOrEmpty(row("TRS Combined Start Date").ToString)).AsEnumerable.Select(Function(row) row("TRS Combined Start Date").ToString).distinct().tolist()

  2. filter dt where trsList doesnt contain Combined Start Date

dt.AsEnumerable.Where(Function(row) Not listTRS.Contains(row("Combined Start Date").ToString)).CopyToDataTable

1 Like

@Rajeswari24 Thanks again for your response and help. I am kind of struggling I have attached .xmal file. Can you please help me out to fix it.

Main.xaml (9.9 KB)

really appreciate your help

Hi @ravindar.singh
Request you to provide accurate Input Excel and Expected output Excel. :slightly_smiling_face:

Hi @ravindar.singh,

After going through your xaml got to know that we should remove all the rows for which “TRS combined start date” is equal to “Combined start date” and it is not like a single row comparison for “Combined start date” and “TRS Combined start date”. In which case the code I have mentioned above will not work. My bad for that.

I have tried the same solution mentioned like @jack.chan and it worked.

Find this attached xaml for the same.
Main (1).xaml (10.4 KB)

Hope this clarifies your doubt.


1 Like

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