Delete Duplikates

Help Studio
,
1

Hi everyone,

I have a problem. I have an Excel list and there are some duplicates in it. I would like to delete all duplicates but only those in which there is nothing in the last cells. (See Excel example).

Do you have any ideas?

The standard function with Remove Rows did not work.
Excel.xlsx (8.9 KB)

2

Hi @NicoB24

You can use the Remove duplicates activity which helps to delete the duplicate rows in the excel.

Hope it helps!!

3

Hi,

Thanks for your message. I have tried it but unfortunately it does not recognise it as a duplicate because the other columns contain values. Only the first column is the same.

4

Okay @NicoB24

Use the Remove duplicates activity only but modify some options as below,
→ Select the Compare Individual Columns option.
→ Click on Add column and give the Name of the first column in your excel.

Check the below image for better understanding,
image

Hope you understand!!

5

Hi,

I have also tried but unfortunately it deletes some of the wrong duplicates, I am only allowed to delete the rows in which the value is only in the first column and nothing in the second column.
image

6

Hey @NicoB24,

You can use the linq as follows to remove the duplicate rows from the datatable. Also include the required columns to be retained.

dt = dt.DefaultView.ToTable(True,“Column1”,“Column2”)

image

Cheers!

7

Hi, thanks for your message. I also tried it but the lines still remain as in the screenshot above.
image

8

Hey @NicoB24,

You can use a Filter Data Table activity for this. Since I am using the ‘Keep’ option, I have specified the operation as ‘is not empty’. Additionally, I have used the column index ‘1’ under the ‘Column’ parameter. You can either use the column name or index and provide as many conditions as you need.

The activity will remove the rows with empty values and filter the datatable.

image

9

Hi @NicoB24

Try this

image
image900×580 27 KB

DT.AsEnumerable() _
  .GroupBy(Function(row) row.Item(0).ToString()) _
  .SelectMany(Function(Group) If(Group.Skip(1).Any(), Group.Skip(1), Group.Take(1))) _
  .CopyToDataTable()

Input:

image

Output:

image

Regards,

1 Like
10

Thank you very much! This works!

1 Like
11

Hi,

Thanks for your solution. Perhaps you also have a solution if the list is not sorted like the input list. Perhaps you can query whether the 3rd column is empty for the respective duplicate and then delete it. See example:

image

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.