Removing a duplicate and insert into a table with status

Please I have an excel, in my excel I have a column called STATUS, My excel has the following sample values and date
1 2 3 4 5 19-09-2019 09-09-2019
2 2 7 4 5 09-09-2017 10-09-2019
6 7 9 8 3 08-09-2019 12-09-2019
If column BDE have the same value in two rows, then I am to remove the rows, populate in excel and update the status as DECLINED(which makes the first two rows above true) , The ACCEPTED columns should be populated in a different excel.
The second scenario is if column G <= Today’s date, I should update the STATUS column in my excel to ACCEPTED and remove the rows, This ACCEPTED rows should be be populated in a different excel.
The remaining columns should be updated as PENDING.
I am thinking of the way to go about it, and I have been stuck on this. Please any idea?

Hi. Do you want to you want to do first and second scenario at the same time or you want to complete the first scenario and execute the second scenario for remaining row.

I want to execute the first scenario before the second one

From your requirement, i have created test excel sheet with input, Accepted, Declined and Pending rows. Please confirm, the expected results are same for your scenario. PFA.

test_duplicate.xlsx (11.8 KB)

Note: For declined status, i have picked one record from these two rows.
1 2 3 4 5 19-09-2019 09-09-2019
2 2 7 4 5 09-09-2017 10-09-2019


you need both rows for declined status?


Yes, Thank you. This is exactly the scenario, But I want the STATUS in different excel sheets (12.9 KB)
Please refer this.


Thank you @kadiravan_kalidoss
But I keep getting the error below
“RemoteException wrapping System.InvalidCastException: Specified cast is not valid.
at System.Data.DataRowExtensions.UnboxT`1.ValueField(Object value)
at System.Data.DataRowExtensions.Field[T](DataRow row,
String columnName)
at lambda_method(Closure ,
DataRow )” while trying to run in my process

Delete the project file and try to run it…!