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
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.
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
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()
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.
Main.xaml (8.3 KB)
Book1.xlsx (53.4 KB)
hello @vignesh.maruthappan
your issue got resolved i am attaching zip file for your reference.,
Forum.zip (34.8 KB)
Regards
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?
yes, you can apply filter condition for both columns together by putting OR .
regards
@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()
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
@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.
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)
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
If you have more issue so you can create new topic we will help you there ,
regards
thanks.can help to send me the attachment sample.sorry