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

Help Studio
, ,
1

Hi,
How do I take column F - column E and place the results in column G?
Thanks!

image
image1005Ă—105 2.18 KB

2

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,
Kenneth

1 Like
3

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.

image
image1865Ă—459 47 KB

4

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.

5

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
6

@Shannon_Quek

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

Cheers