Remove Duplicate in excel based on column using uipath

Hi, I have a list of data but I need to remove duplicates in my excel spreadsheet based on a column using uipath but i have some empty fields in the column. I have tried some of the examples in the Forum but it will remove all the empty field duplicates in the column. is there a way to remove the duplicates based on a column except the blanks? Currently I am using “dt.AsEnumerable().GroupBy(Function(x) convert.ToString(x.Field(of object)(“Master Account”))).SelectMany(function(gp) gp.ToArray().take(1))”


Can you try with below linq query?

DataTable distinctTable = yourDt.DefaultView.ToTable(true, "Column_Name")

I have tried this but it will only show my column name.


oh ok…

Can you attach your excel here?
tell the column from which you would like to stay unique with records

This is an example of my excel spreadsheet. I would like to remove duplicate at “Master Account” except for the empty fields.

So the end result will be like this in the datatable.


I just performed UI actions by making copy of data, I guess that you will have to do a recording or write macro.

UI Actions in excel - Go to Data menu => click on Remove duplicates => select your column => click on Ok.

You will get expected result.
same above, you will have to record in uipath.


Can you try the following expression?

dt.AsEnumerable.GroupBy(Function(x) convert.ToString(x.Field(of object)("Master Account"))).SelectMany(Function(g) if(g.Key is Nothing OrElse g.Key.ToString="" ,g , g.Take(1))).CopyToDataTable


thank you.

1 Like