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)
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
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
@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
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
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
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:)
1 Like
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.