Delete row based on value present in col0 and col1

Hello,

I need to delete the row(0) which contains single char or alphabet e.g. E,K,P only if row(1) does not contain R-IND in it.
tried many ways, please help me with linq query.

TIA.

Data_New.xlsx (10.2 KB)

@1a2bc346dgjk7544ebjkkbvf

use this expression

dt.AsEnumerable.Where(Function(r) r(0).ToString.Equals(system.text.RegularExpressions.Regex.Match(r(0).ToString,“[A-Z]{1}”).Value) And r(1).ToString.Contains(“R-IND”)).CopyToDataTable

@Shiva_Nikhil
thanks for reply but we need to retain other rows too, which has miscellaneous values

If condition
dt1.AsEnumerable.Any(Function(x) x(1).ToString.Contains(“R-IND”))

Then part will give you required output
dt1.AsEnumerable.Where(Function(x) x(“Status”).ToString.Contains(“R-IND”)).CopyToDataTable

Else part you can add steps if no data matching your requirement is found
Add another If
If condition
dt1.AsEnumerable.Any(Function(x) not x(1).ToString.Contains(“R-IND”))

Then part will give you required output
dt1.AsEnumerable.Where(Function(x) not x(“Status”).ToString.Contains(“R-IND”)).CopyToDataTable

Else part you can add steps if no data matching your requirement is found

refer the below flow for reference if required
dummy.xaml (12.0 KB)

if you want remaining data no matching your requirement create another data table variable and assign the below code in else part
dt1.AsEnumerable.Where(Function(x) not x(“Status”).ToString.Contains(“R-IND”)).CopyToDataTable

@1a2bc346dgjk7544ebjkkbvf

use this after the above query

dt.AsEnumerable.Except(dt1.AsEnumerable,system.data.DataRowComparer.Default).CopyToDataTable

to get the unmatched data

Hi,

you can use below query

dtFiltered = dt.AsEnumerable().Where(Function(row) (row.Field(Of String)(“Column1”).Length = 1 AndAlso row.Field(Of String)(“Column2”).Contains(“R-IND”))).CopyToDataTable()

thanks @Shiva_Nikhil Please check revised sample file attached

thanks @neha.upase Please check revised sample file attached

thanks @Unnathi_Bhat Please check revised sample file attached

@1a2bc346dgjk7544ebjkkbvf

on what basis we need to delete the row

@Shiva_Nikhil -
if there is single char in col A, then there should be R-IND in col B, or else delete such rows, which has single aplhabet and has no R-IND in colB.

Hence, need to delete 9, 13, 23 row number , but rest of the data should maintain as it is

@1a2bc346dgjk7544ebjkkbvf

refer this you will get the expected output

filterdt=dt.AsEnumerable.Where(Function(r) r(0).ToString.Length=1 And not r(1).ToString.Contains(“R-IND”)).CopyToDataTable

finaldt=dt.AsEnumerable.Except(filtered_dt.AsEnumerable,system.data.DataRowComparer.Default).CopyToDataTable

use write range and pass the finaldt in the write range wrokbook

Sequence.xaml (7.4 KB)

dummy.xaml (11.0 KB)

if condtion
dt1.AsEnumerable.Any(Function(x) not (((x(0).ToString.Length.Equals(1))) And not x(1).ToString.Contains(“R-IND”)))

Then part assign
dt1.AsEnumerable.Where(Function(x) not (((x(0).ToString.Length.Equals(1))) And not x(1).ToString.Contains(“R-IND”))).CopyToDataTable

Refer attached xaml

1 Like

@Shiva_Nikhil

It’s working but I think duplicate values in column 0 also getting removed .I need rest of the data as it is.
Data_New.xlsx (11.5 KB)

Here I have added few duplicates in col0 which should be as it is
It will be a great help if u check once again

@1a2bc346dgjk7544ebjkkbvf

hi, i just changed the query

dt.AsEnumerable.Where(Function(r) r(0).ToString.Length=1 AndAlso not r(1).ToString.Contains(“R-IND”)).CopyToDataTable

Sequence.xaml (8.5 KB)

Kindly try with this

Hope this helps

1 Like

Thanks @Shiva_Nikhil , however got the solution from @Unnathi_Bhat

Thanks @Unnathi_Bhat , This solution works as expected:)

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