Excel Multiple filter conditions

Help Activities
, ,
1

Hi All,
I have following requirement. I tried different options like Filter record, Filter record with Select, Filter Record in Linq. but I don’t get it. All the data is in one sheet.
(1) remove records from Status column where Status is Receipt and State
(2) remove records from “Payment” column where Payment is Check and Status is Void or Stop Payment. Eg:
Payment Status
Check Void
Stop Payment
(3) remove records from “Plan” column where value is 10 or 10A or 10B

Sheet has 843272 or more rows

I tried all the options but no luck.

2

Hi @khooshbu.jani

What is the error you are facing?

3

it is not getting filtered.
i just attach the screen shot

image
image812×578 29.7 KB

4

Hi @khooshbu.jani
giving value in the form of array doesn’t work… you should give it seperately

5

in your excel sheet may be your data have white spaces or not having specified format
this also become reason for not working may be you can try with macros or VBA script that will work. go to your excel and filter by manually by record macros and use it

6 
DT.AsEnumerable().Where(Function(row) Not(row("PAYMENT TYPE").ToString.Contains("Check")) AndAlso 
Not(row("STATUS").ToString.trim.Contains("Void") Or row("STATUS").ToString.trim.Contains("Stop Payment") Or row("STATUS").ToString.trim.Contains("Check Void") Or row("STATUS").ToString.trim.Contains("10") Or row("STATUS").ToString.trim.Contains("State"))).CopyToDatatable()

You can modify this linq to suits your needs

7

macro is not the requirement of client. They would have done that long back. They need UiPath to work this

8

Thank you Soooooooo much. I got it. But when I run the query it removes CHECK-RH also from the column. So i changed to = instead of contains. But now it is deleting records of CHECK-RH also.

DT.AsEnumerable().Where(Function(row) Not(row(“PAYMENT TYPE”).ToString = (“Check”)) AndAlso
Not(row(“STATUS”).ToString.trim.Contains(“Void”) Or row(“STATUS”).ToString.trim.Contains(“Stop Payment”) Or row(“STATUS”).ToString.trim.Contains(“Check Void”) Or row(“STATUS”).ToString.trim.Contains(“10”) Or row(“STATUS”).ToString.trim.Contains(“State”))).CopyToDatatable()

9

use excel as db How To Use Excel As Database – In UiPath – ExcelCult

SELECT * FROM [Sheet1$] WHERE ([Status]<>'Receipt' AND WHERE [Status]<>'Receipt') AND (NOT([Payment]='Check' AND [Status]='Void') OR NOT([Payment]='Stop' AND [Status]='Payment')) AND ([Plan]<>'10' OR [Plan]<>'10'A OR [Plan]<>'10B')

you can test , change ,correct the query string, I am not sure the string i have given will give correct results

inorder to test the SQL query in normal easier way, try these :

  1. Open a new DB file using MS access

  2. Add a new data source(External data) , and navigate to from excel file and pick your input file

  3. click “Create” tab->“Query Design”

  4. right click on the new tab and click SQL View
    image

  5. Now you can type in any SQL query and run that using “Design”->“Run” button in the ribbon

Hope this helps :slight_smile:

10

@Surabh and Praveenkumar.

Thank you all these was helpful