Deleting rows from a sheet after filtering

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.

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

@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

Hi @susbasu,

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

Regards,
Arivu

@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

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 “!” ?

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

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.

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.

  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.

Hi @susbasu,

Refer this one
DataTable filtering with expressions

Regards,
Arivu

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