I have an Excel workbook where I need to do the following operations -

- Filter Sheet 1 by a value in Column C.
- Copy the filtered data and paste it in Sheet 2.
- Delete the filtered data from Sheet 1.

How can we achieve this ?

Thanks.

I have an Excel workbook where I need to do the following operations -

- Filter Sheet 1 by a value in Column C.
- Copy the filtered data and paste it in Sheet 2.
- Delete the filtered data from Sheet 1.

How can we achieve this ?

Thanks.

You would need to read the sheet one in data set .

- Read the sheet 1 in data set and apply select filter on the column and value desired . ( Datatable.select(‘columnname’= “value”)
- Resultant dataset should go into sheet 2 … so is a write range to put in the result of filter into sheet 2

3 . Create another data table with negative of first select eg Datatable.select(‘columnname’ != “value”) and over write that in sheet 1

How to use filter data table ? Filter data table

@susbasu

Let us take You have have an excel sheet and read it by using read range activity and store it in a datatable **dta**

Now let us take new datatable variable **dt1** for filtered data

dt1=(From p In dta.Select()

where Convert.ToStringf(p.Item(“Column C”)).Equlas(“Value”)

Select p).ToArray.CopyToDataTable

Now write dt1 in sheet 2 by using write range activity.

Now to delete the filtered rows

dt2=(From p.In dta.Select

where **not** Convert.ToString(p.Item(“Column C”)).Equals(“Value”)

Select p).ToArray.CopyToDataTable

Now write dt2 to get the datatable without filtered data.

Regards,

Mahesh

Thanks Mahesh! Let me try this. Can you please explain what is “p” here ? And is it “From p.In” ?

Regards,

Susmit

Use this one

`dt2=(From p in dta.Select where not Convert.ToString(p.Item(“Column C”)).Equals(“Value”) Select p).ToArray.CopyToDataTable`

Regards,

Arivu

1 Like

Thanks @arivu96 ! This works! How can we apply multiple filters using this code ?

I need to filter 1 column by 5 values and 3 other columns by 1 value each.

Thanks!

can you give the sample inputs and what is your expected ouput

Regards,

Arivu

@susbasu

first create list of values on which you want to filter the column lets take **ListA**

Then use the query like this

dt1=(From p in dt.Select

where ListA.Contains(Convert.ToString(p.item(“Column 1”))) and Convert.ToString(p.item(“Column 2”)).Equals(“Value 1”) and Convert.ToString(p.item(“Column 3”)).Equals(“Value 2”) and Convert.ToString(p.item(“Column 4”)).Equals(“Value 3”)

Select p).To Array.CopyToDataTable()

Regards,

Mahesh

1 Like

I need to filter these columns by the following values -

Column C by values “1001”, “1002”, “1003” , “1004” and “1005”

Column D by value "Completed’

Column E by value “True”

Column F by value “Verified”

I need to apply all these filters at once (in one single line of code).

Hi @susbasu,

`Dt=Dt.Select("[Column C] in ('1001', '1002', '1003' , '1004' ,'1005') AND [Column D]='Completed' AND [Column E]='True' AND [Column F]='Verified'").CopyToDataTable()`

Regards,

Arivu

2 Likes

@susbasu

you can also try like this

dt1=dta.Select(“[Column C] In (‘1001’,1002’,‘1003’,‘1004’,‘1005’) and [Column D] =‘Completed’ and [Column E]=‘True’ and [Column F]=‘Verified’”).ToArray.CopyToDataTable

Regards,

Mahesh

1 Like

Thanks!

Again, I need to delete all these filtered rows from dt.

Do I need to add a “not” or a “!” ?

Got the solution. Used a “not =” for all filters and instead of “AND”, used an “OR”. That gave me the original datatable minus the filtered rows.

yes you are right.

`Dt1=Dt.Select("[Column C] not in ('1001', '1002', '1003' , '1004' ,'1005') AND [Column D]<>'Completed' AND [Column E]<>'True' AND [Column F]<>'Verified'").CopyToDataTable()`

Regards,

Arivu

Hey @arivu96 & @MAHESH1 ! Need one more help from you guys! Let me explain the problem -

I have a Master workbook (Workbook 1). Sheet 1 contains say 20 columns and 2000 rows. Column 3 is 'Vendor Number".

I have another workbook (Workbook 2) of a similar structure (headers). Sheet 1 contains 5 columns and 5 rows as of now. Column 3 is again “Vendor Number”. This Column 3 is dynamic.

- I need to filter Column 3 of Workbook 1 using the values in Column 3 of Workbook 2 and dump the data into a new sheet in Workbook 1.
- All filtered rows of Workbook 1 need to be deleted.

How can we achieve this ?

Please guide.

Thanks.

@susbasu

store workbook 1 sheet 1 in variable dt1

store workbook 2 sheet 1 in variable dt2

Then take a list variable List A

List A=(From p In dt2.Select

Select Convert.ToString(p.Item(“Vendor Name”)).ToList()

**For filtering the datatable**

dt3=(From p In dt1.Select

Where List A.Contains(Convert.ToString(p.Item(“Vendor Name”)))

Select p).ToArray.CopyToDataTable

**For deleting the filered rows**

dt4=(From p In dt1.Select

Where not List A.Contains(Convert.ToString(p.Item(“Vendor Name”)))

Select p).ToArray.CopyToDataTable

Use Write range activity to write in excel sheet.

Regards,

Mahesh

1 Like

Thanks a lot @MAHESH1 ! Works perfectly !

Can we color rows based on filters ?

I need to color rows in Column A based on a filter in Column E. For eg. if the value in Column E is “Y”, I need to color the corresponding row in Column A with Yellow.

Currently I have been doing this with a For Each Row and then a Set Color Range and it takes around 3 minutes to color a set of 3200 rows. Is there a faster method to achieve this ?

Thanks!