Remove Duplicate with condition

Hi @arivazhagan_mathivan
I did reread all the posts and confirm to @amaresan. Based on your second table all N are to remove. And this would be the simplest (e.g. FilterTable or LINQ)

Let me reformulate your requirements, maybe you are looking for some different:

  • If a N row ABI # Value exits in a NON-N row ABI # Value then we got a Duplicate
  • Mission: remove the duplicates (means N rows)

grafik
N-AA exists not in NON-N so keep, Keep Y-DD as well, Keep NON-BB,CC
Remove N-BB, N-CC

So this is possible by following:
Identify the Duplicates (defined as above)
Remove them from Datatable

Duplicate Identification:
Assign Activity:
To: DuplicatedNs of DataType: IEnumerable(Of DataRow)
Value:
(From n In dtSample.AsEnumerable.Where(Function ( r ) r(“Pending”).ToString.Trim.Equals(“N”))
Join y In dtSample.AsEnumerable.Where(Function ( r ) Not r(“Pending”).ToString.Trim.Equals(“N”))
On n(“ABI #”).ToString.Trim Equals y(“ABI #”).ToString.Trim
Select n).AsEnumerable

Removal
Assign Activity
To: dtFiltered of Datatype: DataTable
Value:
dtSample.AsEnumerable.Except(DuplicatedNs).CopyToDataTable

Kindly Note: CopyToDataTable is throwing an exception in case no rows are to keep

Let us know if it is working