Compare two excel workbooks,remove the row if found and add to the other excel workbook


I have 2 excel workbooks namely “1.xlsx” and “2.xlsx”.

Based on Column B value in 1.xlsx , search for values from each row in this column with column B value in 2.xlsx.

If the values are found in 2.xlsx then the row that matched from 1.xlsx should be removed and added to 2.xlsx.

Use Read Range activity and get the data to datatables.
Loop through using ForeachRow activity, Compare and add row to the datatable
write back to excel with the updated datatable

I have done that . But I have to remove the matched row also. I have used Remove data row activity . But it removes for the last matched row only .

If you didnt find any solution, please follow below
Assume 1.xlsx moved to dt1 and 2.xlsx to dt2
Take copy of dt1 and dt2 to dtA and dtB respectively- so totally you have four data tables.

Foreach dtA
write linq query to check row(“B”) in dtB and get the row
if row is not nothing
delete row from dt1 and import it to dt2

delete dtA and dtB
Final values will be in dt1 and dt2 that you can write back to excel.
please let me know if you have any questions.