Excel duplicates and sum the amount

Hello All,
I have an excel file in which Output sheet “PolicyNo” matches in Sheet1 and Column “TypeOf” is Policy then add the amount from “Total_Premium” and write in Output sheet in “Inception_Premium” and if the column “TypeOf” is endorsement then add the amount from “Total_Premium” and write in Output sheet in “Tot_Endorsement_Premium”. How to do this? Please help me in this issue.
Corp_Filter.xlsx (457.3 KB)


You can try this

use this in invoke code activity and send dt1 as in parameter which is the sheet1 and dt2 which is the outputdata as in/out parameters

dt2.AsEnumerable.ToList.ForEach(sub(r) r("Inception_Premium") = If(dt1.AsEnumerable.Where(function(x) x("PolicyNumber").ToString.Equals(r("PolicyNumber").ToString) and x("TypeOf").ToString.Equals("Policy")).Count>0,dt1.AsEnumerable.Where(function(x) x("PolicyNumber").ToString.Equals(r("PolicyNumber").ToString) and x("TypeOf").ToString.Equals("Policy")).Sum(function(x) Cdbl(x("Total_premium").ToString)).ToString,""))

dt2.AsEnumerable.ToList.ForEach(sub(r) r("Tot_Endorsement_Premium") = If(dt1.AsEnumerable.Where(function(x) x("PolicyNumber").ToString.Equals(r("PolicyNumber").ToString) and not x("TypeOf").ToString.Equals("Policy")).Count>0,dt1.AsEnumerable.Where(function(x) x("PolicyNumber").ToString.Equals(r("PolicyNumber").ToString) and not x("TypeOf").ToString.Equals("Policy")).Sum(function(x) Cdbl(x("Total_premium").ToString)).ToString,""))


Hello Bro,
Here I’m getting error as dt2 is not declared due to its protection level. How to resolve this issue.


Dt2 is to be added as input /output argument in invoke code did you add?


After passing dt2 as In/Out argument

You need to pass dt1 also

It’s still the same issue.

Can you please share me the sample workflow here.?


please declare like this and pass the datatble variables

Sequence2.xaml (5.9 KB)


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