Remove Duplicate in excel based on column using uipath

Help Studio
1

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))”

2

@anonymous3,

Can you try with below linq query?

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

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

4

@anonymous3,

oh ok…

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

5

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

6

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

7

@anonymous3,

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.

8

Hi,

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

Regards,

9

thank you.

1 Like