Hi how do I tabulate between 2 columns and place it in the third?

How do I take column F - column E and place the results in column G?

Hi @Shannon_Quek ,

After you read and capture the whole datatable, you call loop through it.

To pass on the value on the column G, you add an Assign activity inside your For Each Row in a DataTable activity:

CurrentRow(“Total Labor Price”) = CInt(CurrentRow(“Total Material Price”).ToString.Trim) - CInt(CurrentRow(“Total Repair Fee”).ToString.Trim)

Note that I have converted the row values of columns E and F to Integer to perform the calculation.

Hope this helps. Happy automation!

Kind regards,

1 Like

Another way to do it is to directly get the count of the rows in the sheet and use write cell to update all at once by passing the rowcount+1 value in the range of write cell.

It is always better to pass the excel to a dt in system and do what @kennbalobalo suggested, you never want to write and perform operations directly in excel.

Hi @Shannon_Quek ,

Try this in invoke code activity. Keep dt1 as in/out argument.

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“Total Labor Price”)= CDbl(row(Total Repair Fee) - CDbl(row(Total Material Price))

Hope it helps.

1 Like


Two straight forward ways

  1. Use write cell on "G2" with value as "=F2-E2" then use autofill range and formula gets filled till end and you would get the difference
  2. Use read range and read data into datatatable then use assign with dt.Columns(5).Expression = "[ColumnNameOfF]-[ColumnnameofE]" and write the data back using write range

Hope this helps