Compare data 2 excel

[fairymemay (noona)]

I have 2 excel file.

1. file input as below.
   

2. file chk data.
   

If column APAPNO in file input found in file chk data and column Status = FL—> Delete row in file input.

Please guide me about it.

[gabrielribas4 (Gabriel Ribas)]

Hey @fairymemay!! Lets go to the solution!! I created a spreadsheet with your data. It is important that for this type of question always send input and output samples.

• Step 1: First of all we are going to read these two tables. The first one we will store in a variable of type DataTable “dt_input” and the second in “dt_fileChk”.
  

  

• Step 2: Let’s create a variable of type DataTable “dt_result” which will be the result table with the deleted rows of interest.
  

  

• Step 3: Let’s clone the original table (dt_input) to the result table (dt_result) because as we are going to iterate over the dt_input, we cannot remove rows from it while we iterate.
  

  

• Step 4: Let’s iterate row by row in our table “dt_input” and we will look for the existence of the value sought in table “dt_fileChk” through the activity of “Look up DataTable”. If the value exists, we will extract its “Status” and check if it is “FL”. I will not delve into the lookup activity, if you have questions, ask here.

Properties:

• Step 5: Checking if the value exists and if its Status is equal to “FL”
  

  

• Step 6: If the condition is true, let’s use the “Filter DataTable” activity to remove the row of interest.
  

  

• Step 7: Writing the result for verification.
  

  
  
  
  

The .xaml.
Main.xaml (15.5 KB)

The .xlsx.
Test.xlsx (12.4 KB)

Hope it helps!

[gabrielribas4 (Gabriel Ribas)]

@fairymemay If this resolved your issue, please mark as solved and close the topic! Thanks!!

[fairymemay (noona)]

@gabrielribas4 If I insert data as below.

Output in sheet must show as below.

But now not show and error as below.

File excel(input)
Test.xlsx (13.9 KB)

[gabrielribas4 (Gabriel Ribas)]

Fixing the .xaml! Sorry for the silly mistakes, it’s tired rss.

Its not .Clone() its .Copy()

Use ‘AndAlso’ instead ‘And’

Put ‘Write Range’ outside of ‘For Each Row’ Scope

Now you can run your solution!
Main.xaml (15.5 KB)

[fairymemay (noona)]

@gabrielribas4 Can write output replace old data in same sheet ?