Delete duplicate row base on some condition

Hi Everyone.

I have Input Table as below picture.
image

I want to check the duplicate row and delete base on conditions if they same value at A col, B col and any the value at D col = “OK”… then it will delete the first row and keep last row.

The output Table as below:
image

I have Linq code as below… how to add more the condition if any D Col = “OK”

(From row In Table.AsEnumerable()
Group row By k1= row(0).toString.Trim,k2= row(1).toString Into grp=Group
Let r = grp.last
Order By Array.IndexOf(Table.AsEnumerable.toArray,r)
Select r).CopyToDatatable()

Thanks in advance!
Book1.xlsx (8.9 KB)

Can you just correct me if I understand wrong…
You wanted to delete the row from Data table if
(Col A and Col B have same value) OR (Col D have OK) then we need to delete the row.
Please confirm.

1 Like

Hi Bro.

Col A and Col B have same value AND Col D = “OK”… then will delete the duplicate row but keep last.

@Mr.H

So you can use the below query: It will give you the data which matches ur condition.

DT.AsEnumerable().Where(Function (row) row(“item”).Equals( row(“quantity”)) And row(“status”).Equals(“OK”)).CopyToDataTable

Note: DT is the Table table variable that has to carry all the data.
Capture

Or you can refer to the Xaml file. Which gives you what you want.

Main.xaml (5.5 KB)

1 Like

Hi Bro.

The issue happen as below:

And how to Sum “quantity” if the conditions are met? I have made the below code but i dont know how add the condition D Col = “OK” in my code… if any row in D Col = “OK” then it will Sum

(From d In dtSample.AsEnumerable
Group d By k1=d(“item”).toString.Trim, k2=d(“quantity”).toString.Trim Into grp=Group
Let s = grp.Sum(Function (x) Convert.ToDouble(x(“quantity”).toString.Trim))
Let ra = New Object(){k1, s, grp(0)(2), grp(0)(3)}
Select dtResult.Rows.Add(ra)).CopyToDataTable

Thanks you!

Avoid the No Data Rows Error. you can check row count first. If it have a data then only you move forward.

DataTable.Rows.Count > 0

Where DataTable is the Variable which have the Data.

Second Issue : Sorry I don’t understand the Question. We need to Sum for Colum B

1 Like

Hi Bro.

May you didn’t understand my mean…

As my datatable, i have two duplicate row ( Yellow Highlight ) have same value at “item” Col and “quantity” Col and one row has “OK” in D Col… then it will Sum the “quantity” Col and delete the first row ( keep last ).

image

image

Anyone has any solution for this case?

Thanks in advance!

I have tried to make this code but it not defined status = “OK” while checking duplicate row

(From d In Table.AsEnumerable
Group d By k=d(“item”).toString.Trim Into grp=Group
Let gf = grp.Where(Function (x) x(“status”).toString.ToUpper.Trim.Contains(“OK”)).DefaultIfEmpty(grp.First).toList
From g In gf
Group g By k1=g(“item”).toString.Trim Into grp2=Group
Let gf2 = grp2.First
Order By Table.Rows.IndexOf(gf2)
Select gf2).CopyToDataTable