Deleting rows from a sheet after filtering

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

23

Hi @susbasu,

Refer this,

Regards,
Arivu

24

@susbasu

What I suggest you is First Extract the Column E values and store it in a List A

Then For Each loop for the List
Use If Condition inside For Each and give item.Equals(“Y”)
Then Use Set Color Change activity in then Part
If your values in columns E are Unique then use
Range as “A”+ListA.IndexOf(Item).ToString
If your Column values are not Unique then initialise an integer inta with value one
Use Range as “A”+inta.ToString
After Set Color Change activity increment the value of inta by 1
inta=inta+1

Regards,
Mahesh

25

@susbasu, You can also try this Excel Extended Activities,

https://forum.uipath.com/t/excel-extended-activities/23457?u=dominic

Regards,
Dom :slight_smile:

26

Thanks @MAHESH1 ! Got 2 queries regarding filtering a data table and writing filtered data into new sheet as you mentioned before.

  1. Suppose if the filtered data table contains no rows, I don’t want the new sheet to be created then. How do I ensure that the bot moves to the next item and does not stop ?

  2. How do I use a “less than ( < )” in a filter ?

Say for e.g. in the code -

dt1=(From p in dta.Select()
where Convert.ToStringf(p.Item(“Column C”)).Equals(“Value”)
Select p).ToArray.CopyToDataTable

Here if “Value” = 100 , I want to filter Column C by rows that are less than 100.

How can we do this ?

Thanks!

27

Hi @susbasu,

Dt=Dt.Select("ColumnC > '"+value+"'").CopyToDataTable()

Regards,
Arivu

28

@susbasu

  1. Suppose if the filtered data table contains no rows, I don’t want the new sheet to be created then. How do I ensure that the bot moves to the next item and does not stop ?

just add one if condition
and enter dt.Select().ToArray.Count>0
if its greater than zero then it contains some value so u create new sheet inside then condition else do your other requirement.

2.How do I use a “less than ( < )” in a filter ?

Dt=Dt.Select(“Convert(ColumnC, System.Int32)> “+value+””).CopyToDataTable()

Regards,
Mahesh

29

Hi @MAHESH1,

If ColumnC is already integer datatype no need to convert it.

Dt=Dt.Select("convert(ColumnC, System.Int32)<"+value+"").CopyToDataTable()

Regards,
Arivu

30

@arivu96
Since he is extracting from excel most probably it won’t be in integer format. I think so :grinning:

Regards,
Mahesh

31

It’s a date column in the format d-mmm-yy.

I need to filter dates prior to 1-Jun-16.

32

@susbasu

dt1=(From p in dt.Select
where DateTime.ParseExact(p.Item(“Column Name”).ToString,“d-MMM-yy”,System.Globalization.CultureInfo.InvarientCulture)<DateTime.ParseExact(p.Item(“1-Jun-yy”,“d-MMM-yy”,System.Globalization.CultureInfo.InvarientCulture)
Select p).ToArray.CopyToDataTable

Regards,
Mahesh

33

Getting compilation errors.

34

@susbasu,
Try this
dt1=(From p in dt.Select where DateTime.ParseExact(p.Item(“Column Name”).ToString,“d-MMM-yy”,System.Globalization.CultureInfo.InvarientCulture)<DateTime.ParseExact(“1-Jun-16”,“d-MMM-yy”,System.Globalization.CultureInfo.InvarientCulture) Select p).ToArray.CopyToDataTable()

Regards,
Arivu