Sort column and select row that contain keyword

Hi,

I have this kind of column.
image

What i want to achieve is:

1)sort the column
2)Pick the first and last row which contain POL
3)Pick the first and last row which doesnt contain POL

Is there any idea how to achieve this?

Thanks!

Hi @mashy2

Check this query for sort

(From x In datatablevariable .AsEnumerable()
Order By convert.ToString(x(" Column Name “)),convert.ToString(x(” Column Name "))
Select x).CopyToDataTable()

Next in for each

use row(“Column Name”).ToString.contains(“POL”) or row(“Column Name”).ToString.contains(“PRP”)

Thanks
Ashwin S

Hi @mashy2

Use Select

New Dt=Dt.Select(“[ColumnName]=‘POL’ or [ColumnName]=‘PRP’”).CopyToDatatable()

Thanks
Ashwin S

Sorry, what i meant is:

How to select the first row and last row that contain “POL” and after that
select the first row and last row that contain “PRP”

Thanks!

Hi @mashy2

Check this linq

from line in Datatable.select where(line.ColumnName<>“POL”) select line).ToArray()

Thanks
Ashwin S

@mashy2
I am not sure I got your requirements correctly.

how to select the first and last column
Column is irritating me

Lets assume following:

  • Excel is readin in datatable
  • You are interested on
    ** First, Last row where a particular Column contains the value POL or PRP

this can be done directly with First or Last Statement, Unfortunately it requires 4 Statements, due the functions returning only 1 row.

sample for First POL: dtSample.AsEnumerable.First(Function ( r ) r.Item(2).ToString.Contains(“POL”))
sample for First PRP: dtSample.AsEnumerable.First(Function ( r ) r.Item(2).ToString.Contains(“PRP”))
sample for LastPOL: dtSample.AsEnumerable.Last(Function ( r ) r.Item(2).ToString.Contains(“POL”))
sample for LastPRP: dtSample.AsEnumerable.Last(Function ( r ) r.Item(2).ToString.Contains(“PRP”))

each will return a single System.DataDataRow Object

1 Like

Hello PPR,

Does the r stand for row ?

r is like an adhoc variablename and can be asigned by you

Thank you so much!

Apologizes for confusing statement.

How to convert the data row object to string so i can search in application?

Thanks so much!

@mashy2
Lets assume you are interessted on “columnC” value then use an assign and grab the string with

YourDataRowVariable(YourColumnIndexOrName).toString

@mashy2

You want to convert Datarow as String this is Your requirement? correct me if I’m wrong

for that you can use String.join method

string.Join(“,” ,DataRow.Itemarray)

Example
string.Join(“,” ,Dt.row(0).Itemarray)