For each row in excel, add value in preceding row in the same column

Hello,

I’ve looked through the forum but can’t seem to find this. e.g i want B1 = A1+A2, B2= A2+A3, B3=A3+A4, etc… I’ll appreciate any help i can get on this.

Thank you

image

Hii…

1.Read the excel file to a datatable to find it’s row count.
2. Create a temporary variable to act as a row index, inside a loop with condition “index <=DT1.Rows.Count+1” with it’s initial value as 1.
3.Add a Write cell activity with range "B"+ index.ToString and value "=SUM(A"+index.ToString+"+A"+ (index+1).ToString+")" . Then increment the index value by1.

For your reference, please go through the attached workflow.B1 = A1 A2.xaml (8.2 KB)

Best Regards,
Nimin

2 Likes

Thank you nimin!

@nimin’s solution isn’t bad, but I would normally avoid this approach for data sets that are large.

If you were to do this “manually”, you would put in the first cell =A1+A2, then select B1 down to the last cell, then press Ctrl+d to perform a “Filldown” hotkey which fills all the formulas down in the range.

For that approach in UiPath, you can do the same thing:
—Read Range and store the last row number like: lastrow = dt1.Rows.Count
—Write Cell to “B1” with formula “=A1+A2”
—Use Select Range activity with range: “B1:B”+lastrow.ToString
—TypeInto with Ctrl+d string: "[d(lctrl)]d[u(lctrl)]"

So that’s another idea. Also, you can utilize vbscript with the Invoke VBA activity, and that method works well to do the FillDown.

Regards.

2 Likes

Thank you Clayton. Works perfectly. I did have to include a click activity to save the file though, as i noticed the changes were not being saved

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.