Filter by duplicate column and copy the row in Excel

Hi,

Today i have this situationi have an excel file with X columns and Y rows… i want to delete the rows if an especific colum is duplicated and save it in other Excel, how can i find the duplicate column and delete that entire row?

With this i can filter the rows if the specific column are duplicated:

Where( From q In FiltNewDt.Select()
Where q(“ColumnName”).Equals(p(“ColumnName”))
Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

----But as i said, i need to delete the row only if a column is duplicated, how can i do it?
Thanks, Regards

Hi @panpan1020

So I have this line I have used in one of my update table components.

dtOut.AsEnumerable.GroupBy(Function(r) String.Join("|",in_SearchColumns.Select(Function(col) System.Text.RegularExpressions.Regex.Match(r(col.ToString).ToString,"[A-Za-z1-9](.*)").Value).ToArray) ).Select(Function(g) If(in_boolDeleteLastDups, g.First(), g.Last())).CopyToDataTable

Essentially, it was combining each row item to a string that I was using to determine if it is a duplicate row, and the column names being looked at were in an array of strings called SearchColumns. And, I used a boolean to determine if I wanted to keep the First or Last of the duplicate.

A simpler version might look like this:

dtOut.AsEnumerable.GroupBy(Function(r) r("columnname").ToString ).Select(Function(g) g.Last())).CopyToDataTable

Hopefully that helps.

Regards.

2 Likes