Filter Data table based on criteria

[somesh.r.nagar]

I have a datatable which i need to convert into a different format however before that i need to remove the duplicate data based on below criteria. there are below 3 columns.
Customer Number
Currency
Account Number

Criterias

1. If the same customer number has duplicate currency with Value CZK then the account number with lower series should be considered and other row with higher series should be removed
   2.Any other duplicate currency to be considered in my output
2. Any non duplicate row to be considered in my output.

Below is sample data, the records in bold should be removed

Customer Number	Currency	Account Number
**	451	CZK
451	CZK	61
**	451	CZK
451	IMP	349
451	MUR	354
**	136	CZK
136	CZK	18
136	GHS	270
136	GHS	102
136	HTG	349

Can someone help please

[Yoichi]

Hi,

@somesh.r.nagar

Below is sample data, the records in bold should be removed

Is this correct data? I guess some account numbers are deleted due to **

Regards,

[Yoichi]

Hi,

I estimated the input table is as the following image.

Then, the following expression will work

dt.AsEnumerable.Except(dt.AsEnumerable.Where(Function(r) r("Currency").ToString="CZK").GroupBy(Function(r) r("Customer Number").ToString).SelectMany(Function(g) g.OrderBy(Function(r) CInt(r("Account Number"))).Select(Function(r) r).Skip(1)),DataRowComparer.Default).CopyToDataTable()

Sample
Sample20231129-1aL.zip (7.2 KB)

Regards,

[somesh.r.nagar]

Thank you very much, it worked perfectly a expected.