I am attempting to sum the values in “Variance” until the next blank cell and place the resulting value indexed to the last item in the group in the “Sum” cell. For example, in cell one, the 40960.14 will appear in the first cell. But, in the next grouping the sum of 186095.99+15250.00-195645.99-5700 sums to 0.00 and I want to place that in the cell next to the -5700 in the Sum column. Values in the “Variance” column may change from month to month.
@Chris_Bolin
lets assume following sample data:
Variables:
Sow can build with essential activities following flow:
- iterate over the rows:
- if value is present
- Yes: set the isCounting flag true and sumup the value
- No: if isCounting = true | then: write sum to previous row #else: do nothing
- if iteration is last row and it is in counting mode:
- Yes: write sum to previous row
having result:
find starter help here:
SumUp_ValueSeries.xaml (12.2 KB)
Thanks for the help. This is providing no sums at all. In your code, replacing Variance for Value and resting upon a read Range varianceDt of the table in question. Nothing appears in the Sum column.
@Chris_Bolin
as the refering to the screenshot we do see the sums in the sum column and screenshot datatable was populated from shared XAML.
Maybe there is a difference between the starter help xaml and your implementation. Check for e.g. that the scope of isCounting / tmpSum variable is set top sequence level scope.
Otherwise share the details / your XAML withus and we will have a look on it.
Thanks Peter
The Sum column is set to be empty of values…goal is to have the bot populate the resulting sums in the Sum column
share detail output description of expected result e.g. like the excel filled out from bot after processing. This makes the things clear and reduces ping pongs. Thanks
Point is this represents a time delay in processing. Goal is to provide the business the ability to define true variances that are not subject to time delays so they can research
same as done and shown in the screenshots right?
sum is to write in the row of the last group block.
maybe we do misunderstand you
What you show is exactly what I need. However, having attached the xaml to my previous reply, this is not what is happening. The sum column populates with nothing
no summing is occurring when applied in the xaml to the actual table. Replaced your “Value” in the dt with “Variance” from the excel file (read range). Substituted your row with write cell activity to write the value in your sum column to the excel file with Column of “F” + index.tostring (with an index + 2 assigned earlier in the xaml (varianceDt.rows.indexof(row)+2)
didnt get you, however in such a case do following:
- do stewise debugging- analyze and watch flow
- Not (isNothing(row(“Variance”)) OrElse String.IsNullOrEmpty(row(“Variance”).toString .Trim())) – Add an additional trim to overcome blanks from excel
- index output from for each activity is to prefer instead of indexOf
here we do have a difference:
your xaml:
my xaml:
N2 is about the formating for decimals:
Thanks will try your suggestion…I am not sure I follow your index comment. I assigned an IndexOf earlier in the automation for each body. Will tinker with it and let you know what I come up with. I tried a DoWhile with an incremental counter but was not getting the sum events either - the result was just the variance repeated in the sum column(resulted in a duplication of each number instead of a summed grouping)
Also your write cell should as same be executed as in my code the sum value is set to datatable. We could interpretate your imp as only for the last datatable row the excel cell is written. And if last row is not activated by isCounting flag then nothing will be done.
Its recommended to check this part carefully while step wise debugging
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu