Compare Two Excel Files and Delete Matching Rows in one excel file

Help Activities
, ,
1

I have two excel files A & B, Excel A is attached below

The image is a spreadsheet displaying errors for duplicate invoice numbers, with each row indicating an already-used invoice number for a specific supplier. (Captioned by AI)
The image is a spreadsheet displaying errors for duplicate invoice numbers, with each row indicating an already-used invoice number for a specific supplier. (Captioned by AI)696×730 11.1 KB

In Excel A Column Name Your Value
Excel B is attached Below
image
image1085×738 10.4 KB

In Excel B Column Name Invoice Number

Condition need to check here is when Excel A Your Value column matches with Excel B Invoice Number I need to delete matching rows in Excel B

Please provide your inputs

Thanks in advance
Appreciating your time and patience

2

Check this linq:

dt_ExcelB = dt_ExcelB.AsEnumerable().Where(Function(row) Not dt_ExcelA.AsEnumerable().
Select(Function(a) a(“Your Value”).ToString.Trim).Contains(row(“Invoice Number”).ToString.Trim)).CopyToDataTable()

3

I’m getting error

The image shows a UiPath Expression Editor window with an error message indicating a 'AsEnumerable' method issue involving a data table operation. (Captioned by AI)
The image shows a UiPath Expression Editor window with an error message indicating a 'AsEnumerable' method issue involving a data table operation. (Captioned by AI)1239×614 37.4 KB

A screenshot showing an assign activity with an error in UiPath, attempting to convert 'dt_ExcelB' using the 'AsEnumerable' method. (Captioned by AI)
A screenshot showing an assign activity with an error in UiPath, attempting to convert 'dt_ExcelB' using the 'AsEnumerable' method. (Captioned by AI)617×190 6.65 KB

4

Hi @rsr.chandu

First use read range workbook to read the 2 excels in the datatable dt_ExcelA and dt_ExcelB respectively

Then use assign activity

dt_ExcelB = dt_ExcelB.AsEnumerable().
    Where(Function(rowB) Not dt_ExcelA.AsEnumerable().
    Any(Function(rowA) rowA("Your Value").ToString.Trim = rowB("Invoice Number").ToString.Trim)).
    CopyToDataTable()

Try this
Hope this helps!

6

I tried the above solution but it is not deleting the matching rows in excel b file