Hi -
I want to add two rows values based on condition provided and store in another cell.
Data table looks
Item Value 1 Value 2
ABC 100 200
XYZ 200 500
PQR 500 800
I’m trying to read this data using read range activity. Further, I want to add data from column Value 1 when the item is ABC and PQR. I used the For each item and used if statement item(“item”).tostring.contains(“ABC”). Basically, when its true i.e. item is ‘ABC’, it should store 100 and item equal to XZY - 200. Then 300 as sum into another cell within or another excel.
Not sure how to assign value in this case. Please suggest or if you have any sample workflow. Please share it.
Thanks
1 Like
Fine
hope these steps would help you resolve this
–use a excel application scope and pass the file path as input
–inside the scope use a read range activity and get the output with a variable of type datatable named outdt
–now use a for each row activity and pass the above datatable variable as input
–inside the loop use a if condition like this
row(0).ToString.Equals(“ABC”)
if the condition is true it will go to THEN part where we can use a assign activity like this
int_value1 = Convert.ToInt32(row(“Value 1”).ToString)
or if the condition fails it will go to ELSE part where we can leave it empty
–followed by this if conditon use another IF condition like this
row(0).ToString.Equals(“PQR”)
if true it will go to THEN part where we can use a assign activity like this
int_value2 = Convert.ToInt32(row(“Value 1”).ToString)
where int_value1 and int_value2 are int32 variables defined in the variable panel.
now we can add them with a simple assign activity like this
int_Sum = int_value1 + int_value2
where int_Sum is a variable of type int32, which can be used as a input variable in WRITE CELL activity to write in other cell or even to another excel within a new excel application scope with write cell in it.
hope this would help you
Cheers @anujkaul
Hi,
Just wanted to confirm. If the position of the ‘ABC’ and ‘PQR’ within Item column is dynamic. I mean could be at 5 or 6 row. Could you please confirm if the above will work?
Thanks
1 Like
It will work fine.
This is based on the condition we are assigning value to int_value1 when the condition satisfy it will assign value to variables, iteration will end last row data table.it is not based on the number of rows.
Thanks,
Suresh J
Yah of course buddy it will work for sure
The reason is for each row loop will iterate through each row in the datatable read from the excel
—the if condition used inside the loop will validate each and every row while iterating
—in that case even if the position of the value “ABC” and “PQR” changes the position it will be caught at one spot during iteration and will get added at last as we have the assign activity to sum up them next to it
Hope this would help you
Cheers @anujkaul
Thanks!
However, i’m still facing issue using the above method.
The result i get using message box is 100 (Twice)
It should be 300 and not sure why i’m getting two message boxes.
