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 (…

Hi @NicoB24 You can use the Remove duplicates activity which helps to delete the duplicate rows in the excel. Hope it helps!!

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.

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!!

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]

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!

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

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 conditi…

Thank you very much! This works!

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]

Delete Duplikates

Help Studio

lrtetala (Lakshman Reddy) March 5, 2024, 5:33am 9

Hi @NicoB24

Try this

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:

Output:

Regards,

1 Like

Forum Engagement Daily Reports

How can we avoid dupicates in excel i have only one header row?

Topic		Replies	Views
How to remove duplicates in excel on keywords Help excel , activities , question	8	2534	November 21, 2019
How to delete duplicate rows in Excel Academy Feedback datatable , excel , activities , error	17	8179	July 25, 2020
How to remove duplicate from excel Help studio	5	11133	November 12, 2018
How to remove duplicates in an excel Help	2	2266	February 13, 2019
How remove duplicate name or data in row Help excel , activities , question	14	1961	March 30, 2020

Delete Duplikates

Related topics