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
@susbasu, You can also try this Excel Extended Activities,
Thanks @MAHESH1 ! Got 2 queries regarding filtering a data table and writing filtered data into new sheet as you mentioned before.
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 ?
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”)
Here if “Value” = 100 , I want to filter Column C by rows that are less than 100.
How can we do this ?
Dt=Dt.Select("ColumnC > '"+value+"'").CopyToDataTable()
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()
If ColumnC is already integer datatype no need to convert it.
Since he is extracting from excel most probably it won’t be in integer format. I think so
It’s a date column in the format d-mmm-yy.
I need to filter dates prior to 1-Jun-16.
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)
Getting compilation errors.
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()
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.
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
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 “email@example.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 “firstname.lastname@example.org”.
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 “email@example.com”, again ID 1001 can be mapped to WB and MP with another e-mail “firstname.lastname@example.org” and so on).