Deleting rows from a sheet after filtering

Help
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

35

Thanks @arivu96 ! Regarding the query I had about the filtered table having no rows, I tried to put an if-else condition with

If dt.Select().ToArray.Count > 0 then write range, else move to next item.

But even then it throws “The source table has no rows”.

Any workarounds for this ?

I need the bot to skip writing the sheet if no rows are found, but not fail.

36

@susbasu

Better do like this
Create filtered dt1 as a datarow array then after that check in if condition dt1.Count>0
if true dt1.CopyToDataTable and use WriteRange

Regards,
Mahesh

37

Thanks @MAHESH1 and @arivu96 ! You guys have been very helpful!

I was faced with a problem today and it goes back to the inital query that I had posted (Workbook 1, Workbook2, using item of a column in Workbook 2 to filter Workbook 1).

Let’s consider Workbook 2 has 3 columns (that we need to work upon!) - ID # (Column 1) , State (Column2) and E-Mail (Column 3) apart from the other columns. Now for each row in Workbook 2, I need to look for the same ID value and the same State to find matches in Workbook 1. After this, I need to pick these rows and paste them into a new workbook and send them as an attachment to the E-mail mentioned in the row in Workbook 2.

To explain, let’s say row 1 of Workbook 2 contains ID as 1001 and State as Maharashtra and email “abc@xyz.com”. I need to look for the ID and State columns in Workbook 1 and then look for the rows which give me ID as 1001 and State as Maharashtra. Once I find all those rows, I need to dump them in a new workbook and save it. I will then send that new workbook as an attachment to “abc@xyz.com”.

This process needs to be repeated for all rows in Workbook 2.

How can we do this ? Any help would be greatly appreciated.

Note : One ID can be mapped to either no state (i.e. for one particular ID say 1001, the State column in Workbook 2 is blank, which means details of all states with the corresponding ID 1002 should be sent to the e-mail provided in the corresponding e-mail column) or multiple states (i.e. ID 1001 can be mapped to Maharashtra, Karnataka and TN with a different e-mail such as “pqr@xyz.com”, again ID 1001 can be mapped to WB and MP with another e-mail “def@xyz.com” and so on).