Two Excel compare

I have two Excel 1st contains blacklisted customer & another 2nd contains customer with details
I have to check if 2nd excel contains any blacklisted customer by their Customer Id then remove that record from 2nd excel & print that customer name so how can i do this?
Blacklisted Customer.xlsx (8.0 KB)
Customer detail.xlsx (8.2 KB)
@Sudharsan_Ka @supermanPunch @Kalpesh_Chaudhari

Try Lookup data table and update it the Blacklisted customers in Customer DT.

Then use a filter data table to remove the blacklisted rows.

In case of blacklisted row not found?

Hi @pravin_bindage,

Please find the attached main.xaml. Please mark as solution if it resolves.
Main.xaml (15.1 KB)

Feel free to ask if you are not able to understand the code.

1 Like

Can you once elaborate this syntax. I’m not understanding where is the reference for column of Security no. from blacklisted datatable
in_BlackList.AsEnumerable.Any(Function(x) x(0).ToString.Trim.Equals(CurrentRow(“ISIN”).ToString.Trim))

The 0th index is your first column in blacklist datatable as x(0).ToString.
We can provide exact column name or column index starts from 0.

I get this error now →
For Each Row in Data Table: Collection was modified; enumeration operation might not execute.

Could you share for which activity this error occuring?
You can share screenshot.

Well, you can not modify the DT inside the loop which you are looping on; DT_AllocationFile in your case.
You are using Remove Data Row for DT_AllocationFile while your are looping over it.
You can take DT_AllocationFileCloned = DT_AllocationFile.Copy and then loop on other and modify another.

then which datatable should i take in loop activity & remove data row activity

Loop on DT_AllocationFile and modify (remove) from Copied one.

DT_AllocationFile .Clone will copy only table structure and DT_AllocationFile .Copy will copy the entire table.

Use DT_AllocationFile.Copy while assigning to DT_AllocationFileCloned; since you are using Remove Row but no rows exist in thats why it’s throwing error now.

Yes I’m using Dt.copy

Can i use delete range in loop insted of remove data row. Ultimately my aim is to remove matching row from excel file


Can you try remove row using index?
I have added Delete range from excel sheet logic.

Sorry I’m not getting this logic

In your Remove Data Row, instead of passing CurrentRow to Row property, pass current index to RowIndex property.

1 Like

Now it works