Subtract value from one row with another based on unique identifiers

Hello, I am in quite a pickle. I am relatively new to RPA and I am trying to come up with a linq query or activity that does the following: example__
I have a datatable with two columns, and i am trying to sort the first column by the values as in the diagram above, then subtract the values in column B and C where we have matching values in column A. Column A shows a transaction ID, then column B shows the charge of the transaction and column C shows the gross amount paid. I am trying to come up with column D which will then show the net amount, which is a subtraction of the gross amount minus the charge like in the table below example_

Sort.xaml (10.9 KB)

Check this out, May be that can help.
In this workflow i am assuming only two transaction ID will be similar to each other for subtraction. if we get more than two in first column, then you have to change the logic in IF condition ,use loop.


1 Like

@sachinbhardwaj thank you for the response. Let me try it out.

Do you need to preserve each row for each transaction ID. Just thinking you could create a new datable using group by and sum the two columns, then just take the difference and write to the net column.