Deleting rows from a sheet after filtering

Help
1

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

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

How can we achieve this ?

Thanks.

2

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

  1. Read the sheet 1 in data set and apply select filter on the column and value desired . ( Datatable.select(‘columnname’= “value”)
  2. 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
3

@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

4

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

Regards,
Susmit

5

Hi @susbasu,

Learn LINQ contact so you may come to know what is that.

Regards,
Arivu

7

@susbasu,

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
8

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!

9

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

Regards,
Arivu

10

@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
11

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).

12

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
13

@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
14

Thanks!

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

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

15

@susbasu, keep the original data table ,Store it in the different datatable.

17

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.

Thanks a lot for the solution @arivu96 and @MAHESH1.

18

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

19

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.

  1. 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.
  2. All filtered rows of Workbook 1 need to be deleted.

How can we achieve this ?

Please guide.

Thanks.

20

Hi @susbasu,

Refer this one
DataTable filtering with expressions

Regards,
Arivu

21

@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
22

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!