Dear,
I need to subtract two columns in an Excel table and write the result in a third column.
Column J = I - H
Hello @dvojinovic
You should use a Read Range (Excel/Workbook) activity to read the Excel file.
Then use a For Each Row in Data Table activity to iterate the rows.
Inside this loop you can make the substraction:
Assign CurrentDatarow(9) = CurrentDatarow(8)-CurrentDatarow(7)
Or
Assign CurrentDatarow("ResultColumn") = CurrentDatarow("FirstColumn")-CurrentDatarow("SecondColumn")
Finish off by using a Write Range (Excel/Workbook) activity if you wish to write the result back to the Excel file.
Regards
Soren
Hi @dvojinovic
→ Drag and drop the Use excel file activity and give the path of the file.
→ Inside Use excel file activity use the Write cell activity and give the formula,
=Sum(H2:I2)
→ Use the Auto fill activity.
Then write cell activity to write the formula in first row, auto fill activity to fill through the column.
Hope it helps!!
- Use Read Range and read the data into a Datatable. For example variable
dt - Use below code in Invoke code:
dt.Columns.Add("ColumnResult", GetType(System.Double), "Convert([Column1], 'System.Double') - Convert([Column2], 'System.Double')")
Output:
Write it back to Excel.
Make sure to change column names as per your file.
If needed directly on excel
then simply use B2-A2 and then use auto fill rnage to fill the formula till bottom…change the columns as needed
if you want to do it in datatable then
dt.Columns("NewColumnName").Expression = "[Column1]-[Column2]" give the column names as you need and it should give the subtracted value in NewColumnName column directly for all rows. This can be done using single assign activity
cheers