How to Filter More Items from two columns in Datatable

I need to filter two columns in a data sheet. I am using data filter to save to a new sheet.
Column AJ gets filtered and column AT doesn’t get filtered. Any solution for it.I have tried with AND columns become empty without data. can anyone help me on this

Hi @vignesh.maruthappan

You can try with Linq Query also,
Can you send the excel report if possible and mention what exact data you want.

Before of that please check the sheetname which sheet is you are reading and make sure read range output variable and filter datatable input variable must be same.

1 Like

Hello @vignesh.maruthappan ,
if you want to increase Efficiency and reduce execution time. so you can use linq
you can try,

DataTableVariable = dt.AsEnumerable().Where(Function(row) row.Field(Of String)(“Part Type”).Contains(“System Pipe Fitting”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Fittings”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Accessories”)).CopyToDataTable()

Put your variable name in above linq and if you have any query feel free to ask

Regards,
Dheerendra Vishwakarma

1 Like

hello @yedukondaluaregala
you can see below

Your_DT=in_DtTable.AsEnumerable().Where(Function(row) row.Field(Of String)(“Part Type”).Contains(“System Pipe Fittings”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Fittings”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Accessories”)).CopyToDataTable()

Regards,
Dheerendra Vishwakarma

Hi @Dheerendra_vishwakarma I am getting a error. Do i miss something? Actually the filtered value should be saved in a new sheet in my data table.

dt.AsEnumerable().Where(Function(row) row.Field(Of String)(“Part Type”).Contains(“System Pipe Fitting”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Fittings”) Or row.Field(Of String)(“Revit System Status”).Contains(“Completed”)).CopyToDataTable()

@vignesh.maruthappan

you are getting this error because of double quotation " "
you can remove " " and type it again issue will get resolve.

i am attaching file for your refernce

task.xaml (7.5 KB)

regards

@vignesh.maruthappan
i have edited my response you can check
regards

@Dheerendra_vishwakarma I am getting one more error .I s it possible to check with this file. It would be great if you could check it. thanks a lot.
image
Main.xaml (8.3 KB)
Book1.xlsx (53.4 KB)

2 Likes

hello @vignesh.maruthappan

your issue got resolved i am attaching zip file for your reference.,

Forum.zip (34.8 KB)

Regards

1 Like

Thats great. Thanks for the quick response

3 Likes

@yedukondaluaregala

Happy Automation…

one doubt. So i would be able to add filter for column 2 also with same linq?using OR -column one and column 2 together?

@vignesh.maruthappan

yes, you can apply filter condition for both columns together by putting OR .

regards

1 Like

@Dheerendra_vishwakarma tried out its not filtering. When we use for two columns using OR.Am i missing any step in it?

OutDT.AsEnumerable().Where(Function(row) row.Field(Of String)(“Part Type”).Contains(“System Pipe Fittings”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Fittings”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Accessories”) Or row.Field(Of String)(“Revit System Status”).Contains(“Completed”)).CopyToDataTable()

@vignesh.maruthappan ,

copy column name and required value properly you will get your result,

or if you want to see the linq is working for multiple column put AND and see the difference.

Regards

1 Like

@Dheerendra_vishwakarma Is it possible to show a sample. Because tried out with AND.But i am not able to make it up.Thanks for understanding. :pensive:

hey @vignesh.maruthappan ,

When using LINQ you must set a priority,
You have to use ( ) and you can see in below linq

OutDT.AsEnumerable().Where(Function(row) ( row.Field(Of String)(“Part Type”).Contains(“System Pipe Fittings”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Fittings”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Accessories”) ) Or row.Field(Of String)(“Revit System Status”).Contains(“Completed”)).CopyToDataTable()

and try this one as well

OutDT.AsEnumerable().Where(Function(row) ( row.Field(Of String)(“Part Type”).Contains(“System Pipe Fittings”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Fittings”) Or row.Field(Of String)(“Part Type”).Contains(“Pipe Accessories”) ) AND row.Field(Of String)(“Revit System Status”).Contains(“Completed”)).CopyToDataTable()

Regards

@Dheerendra_vishwakarma Yes i tried it with () Column 1 is filtered and column 2 is not.
Book1.xlsx (52.8 KB)
Main.xaml (8.4 KB)

yes @vignesh.maruthappan

it is working fine try to change the status of second column as “Not Completed” and see the difference

you can see in below attachment

final_linq

If you have more issue so you can create new topic we will help you there ,

regards

1 Like

thanks.can help to send me the attachment sample.sorry