Sum of specific rows of a particular column and write in seperate tab

Help
1

Hi All,
I have excel which has 2 columns.
Input:
image

I am checking if status = test then skip that row.
Now add the value of rest all data with 3 rows at a time. In case the last one is just 1 or 2 value,it’s ok, just add them.
Output.
image
Need to write them in sheet 2.

Any help or xaml will be very helpful.

2

Hi
Kindly follow the below steps that could help you solve your issue
—use a excel application scope and pass the file path as input
—use a read range activity and get the output with a variable of type datatable and name it out_dt
—use a filter datatable activity and pass the above variable as input and in the filter datatable wizard mention the columnname as “abc” and condition as not equal to and value as “test”
And mention the output datatable with a variable defined in the variable panel named finaldt with default value new System.Data.Datatable
—so the filtered datatable finaldt will have the filtered rows without test as value in column status
—now use a assign activity like this
rowscount= finaldt.Rows.Count
Where rowscount is a variable of type int32
—use a build datatable activity and create a table with that one column alone named Total which we want and get the output from this activity named dt of type datatable
—now use a while loop with condition like this
(rowscount+2).Tostring.Equals(out_dt.Rows.Count.Tostring)
—another assign activity like this
Firstvalue = out_dt.Rows(rowscount)(“abc”).Tostring
—similarly second assign activity with the same
Secondvalue = out_dt.Rows(rowscount+1)(“abc”).Tostring
And finally third be like
ThirdValue = out_dt.Rows(rowscount +2)(“abc”).ToString
—inside the loop use a assign activity like this
rowscount = rowscount + 2 at last

—now these three values can be added and given to a variable named let’s say
Finalvalue = Convert.ToInt32(Firstvalue) + Convert.ToInt42(Secondvalue) + Convert.ToInt32(ThirdValue)
Where the finalvalue is a variable of type int32

—now while still being in the for each row loop and next to the above expression use a add datarow and mention the arrayrow property with the column value
Like this
{FinalValue}
And datatable property value be like dt
— now use a excel application scope and pass the file path as input
—use write range activity and mention the datatable as dt and in this write range activity enable the add headers property…

Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @Zahid1

1 Like
3

@Palaniyappan - Thanks a lot for the steps, I tried to follow the same but getting error.
Attached is the xaml. Could you please take a look into the issue.

data.xlsx (10.8 KB) Main.xaml (16.3 KB) project.json (725 Bytes)