Excel Copy duplicate rows into new sheet and delete them form the original sheet

[Siyu_Angela_Kuang]

Hi there,

I am trying to

1. find the duplicate rows of two excel files (“RMreport.xlsx”&“Excel2”) based on “Contract Number”, and then
2. copy duplicate rows into new sheet named “Delete” and
3. delete them form the original sheet (“Overdue_days”) in “RMreport.xlsx”,
   but I don’t know how to do, I need your help. Many Thanks.
   
   

[supermanPunch (Arpan)]

@Siyu_Angela_Kuang Can you provide the two Excel files ?

[Siyu_Angela_Kuang]

Sorry, since I’m a new user, I cannot upload attachment now. Can you provide me your email address so that I can send you the two files by email?

[supermanPunch (Arpan)]

@Siyu_Angela_Kuang Can you maybe provide the Expected Output for the two Excel Data’s that you have shown in the Image ?

[Siyu_Angela_Kuang]

The expected output are composed of two parts, and should be like this:

[supermanPunch (Arpan)]

@Siyu_Angela_Kuang Check this Workflow :
FilterDT.zip (9.3 KB)

This method is using the Datatable Row Selection with the Help of Linq.
I have used two Query’s, One is to Select the records matching the Contract Number and Write the Result to the Delete Sheet and Another Query to Select the records Not matching the Contract Number and Write it to the Output Sheet.

However If you would need the Excel Color Format’s to be preserved as it is, you would need to create an output Template of the same format, and Write the Outputs to that Excel File.

Revert back if it is not the expected output that you needed.

[Siyu_Angela_Kuang]

This is exactly what I need. I really appreciate. Thank you!

[Siyu_Angela_Kuang]

One more question: if I want to remove the “Sheet1” and"Sheet 2" and only keep the “Delete” and “Output” sheet, how can I realize it with UiPath?

[supermanPunch (Arpan)]

@Siyu_Angela_Kuang Actually, Since I had that Input file, I used that excel file itself. You can write to a new Excel File, if that is what you needed.

[Siyu_Angela_Kuang]

Ok, Thanks! One more little Q: keep the previous conditions, what if I want to add one more condition, for instance, “Code” is empty. Output the duplicate rows that does not have code.

I guess I only need to change the assign “drArray = DT1.AsEnumerable().Where(function(row) DT2.AsEnumerable().Select(function(r) r(“Contract Number”).ToString).Any(function(x) x = row(“Contract Number”).ToString)).ToArray” part, but I don’t know how Can you please help?

[supermanPunch (Arpan)]

@Siyu_Angela_Kuang Mostly, You would need to change the First Linq Query to the Below one :

DT1.AsEnumerable().Where(function(row) DT2.AsEnumerable().Select(function(r) r).Any(function(x) x("Contract Number").ToString.Equals(row("Contract Number").ToString) and String.IsNullOrWhiteSpace(x("Code").ToString))).ToArray

The Second Linq Query to the Below one :

DT1.AsEnumerable().Where(function(row) Not DT2.AsEnumerable().Select(function(r) r).Any(function(x) x("Contract Number").ToString.Equals(row("Contract Number").ToString) and String.IsNullOrWhiteSpace(x("Code").ToString))).ToArray

Can you check and revert back if it doesn’t work ?

[Siyu_Angela_Kuang]

It works perfectly! Thank you so much

[Siyu_Angela_Kuang]

Hi Superman,
I have one more Question related to the sheets:
I want to classify data in “DT1” according to different grades provided in DT2:
e.g. if (grade = 1 or 2 and OD = 5-7), add the rows to the “Delete” sheet;
if (grade > 2 and OD = 5-7), add the rows to the “OD5” sheet;
if (grade = 1 or 2 and OD = 11-13), add the rows to the “OD11” sheet.
Please see the screen shot for more explanation:

I guess the logic is to

1. add a column (“grade”) in sheet 1 before “Contract Number” , and then
2. filter the data based on requirements provided? still only need to change the “Assign drArray” part?
   I tried to write it in UiPath by modifying the you gave me earlier, but failed

[supermanPunch (Arpan)]

@Siyu_Angela_Kuang Can you send the excel files , It is mostly similar to the Linq I have given earlier, You might just need to add more Conditions, and additional separate queries for the outputs you require.

[Siyu_Angela_Kuang]

Wow I can send the excels now
Please see the documents as attached.
Overdues.xlsx (11.0 KB) Grading.xlsx (8.3 KB)

[supermanPunch (Arpan)]

@Siyu_Angela_Kuang When I apply the Conditions to get the respective outputs, I get the OD5 Sheet Output and Delete Sheet output correctly. But I only get one row in OD11 Sheet instead of 3 rows. Can you cross and verify if that’s the actual result or Is there more condition to be applied.

[Siyu_Angela_Kuang]

Sorry, in the OD11 sheet, there should only be one row:

[supermanPunch (Arpan)]

@Siyu_Angela_Kuang Check this Workflow :
FilterDT.zip (17.1 KB)

[Siyu_Angela_Kuang]

I just checked. Running with no problem. Thank you!