Remove duplicate row has smallest value at another column

Hi Everyone.

I have datatable as excel file and attached picture.

I want to check and remove duplicate row at col “item” and keep row if it has negative smallest value at col “quantity”

As my datatable, i want to remove the row highlighted yellow.

How to do to add condition into the my Lin.Q code below

(From row In table.AsEnumerable()
Group row By k1= row(“item”).toString.Trim
Into grp=Group
Let r = grp.Last
Order By Array.IndexOf(table.AsEnumerable.toArray,r)
Select r).CopyToDatatable()

Thanks in advance!

image Book1.xlsx (8.2 KB)

@Mr.H,

You can use Remove Duplicates and provide columns u want to,
You can use Filter data table to filter a data.

Cheers,
Pankaj

@Mr.H

A LINQ could look like following:

(From d In dtData.AsEnumerable()
Group d By k1= d("item").toString.Trim Into grp=Group
Let mx = grp.Select(Function ( r ) Convert.ToInt32(r("quantity").toString.Trim)).Where(Function (w) w >=0).DefaultIfEmpty(0).Max()
From g In grp
Where Convert.ToInt32(g("quantity").toString.Trim) >= mx
Order By dtData.Rows.IndexOf(g)
Select g).CopyToDatatable()

Find starter Help here:
Group_1Col_keepNonNegativesAndHighestNegative.xaml (7.1 KB)

1 Like

Thanks you very much!

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