Is there a way to delineate columns for removing duplicates in excel using UiPath?

[ajeffers]

I have a use case with the below example. The criteria for a duplicate is the following:
First Name, Last Name, Facility, Timestamp. When duplicates are found, I want the most recent claim to remain.

I’ve attached the inputs and what output (what is expected).

I also noticed in UiPath, remove duplicates doesn’t delineate what columns, it looks at all columns.

Is there a way to delineate this in the modern activities or a different approach?

Regards,
Alicia

Input:

Output: (should look like this)

[Nithinkrishna]

Hey @ajeffers

Here is the demo for you…

nmnithinkrishna_RemoveDuplicatesFromDT.zip (9.1 KB)

Output

Hope this helps you.

Thanks
#nK

[ajeffers]

Do you think you could walk through why you did what you did? It seems to work, but I am having a hard time understanding.

[Manuel_Dominguez (Mdominguez)]

Hello @Nithinkrishna, your query is great i am very interested in it, i would like to ask you one question about your code.

After use the activity Add Data Column to the dt_Temp, that table is complete with the new records, but i do not see where that table is fill, could you tell me in wich part do you fill the table?

[Nithinkrishna]

Hey @ajeffers @Manuel_Dominguez

Please find the breakdown & its explanation below…

//Selecting Rows by Adding new Comparer Column - Concatenation of columns with which we need to find duplicates dt_Input.AsEnumerable.Select(Function(row) _ dt_Temp.Rows.Add(row.ItemArray.Concat( New Object() { String.Concat(row("First Name").ToString.Trim, row("Last Name").ToString.Trim, row("Facility").ToString.Trim, row("Claim Number").ToString.Trim) } ).ToArray) //Grouping the rows by common Comparer & then taking the latest using order by ).GroupBy(Function(row) row("Comparer").ToString).Select(Function(grp) dt_Temp.Rows.Add(grp.OrderBy(Function(obj) obj("TimeStamp").ToString).Last().ItemArray)).CopyToDataTable

Thanks
#nK

[ajeffers]

I should have noted, the second part of this problem was to give back the latest Timestamp as well. Thank you for explaining this. I just learned about LINQ and I’m going to try this and let you know if it works for me.

[Nithinkrishna]

Cool @ajeffers