Excel duplicates

Help Activities
, ,
1

Hello All,

I have an excel file, in sheet Output policy number contains same in sheet1 then in column “TypeOf” add policy from column “TOTAL_PREMIUM” and write in output sheet in “INCEPTION_PREMIUM”.
Corp_Filter.xlsx (252.7 KB)

1 Like
2

Hello @ISBS_ROBOT

Excel Application Scope (Specify Excel file path)
Read Range (Sheet1) → DataTable1
Read Range (Output) → DataTable2

Assign (Counter = 0)

For Each Row in DataTable1
Assign (PolicyNumber = Row(“policy number”))
Assign (TotalPremium = Row(“TOTAL_PREMIUM”))

For Each Row2 in DataTable2
    If Row2("policy number") = PolicyNumber
        Assign (InceptionPremium = Row2("INCEPTION_PREMIUM"))
        Assign (MergedPremium = InceptionPremium + TotalPremium)
        Assign (Row2("INCEPTION_PREMIUM") = MergedPremium)
        Break (Exit the loop)
    End If
End For

Assign (Counter = Counter + 1)

Next

Write Range (Output) → DataTable2

Thanks & Cheers!!!

3

Hi Can You Clarify

  1. if both sheet1 and Output matches the policy number what we need to do and what we need to write to INCEPTION_PREMIUM sheet

@ISBS_ROBOT

4

In If condition I’m getting an error like “option strict on disallows operands of type Object for operator = Use Is operator to test for object identity”
and in assig activity for Merged premium like “Option strict on prohibits operands of type object for operator +”

1 Like
5

in output policy num contains same num in Sheet1 then within sheet1 column “typeOf” contains policy then add all the policy duplicates with the amount in the column “TOTAL_PREMIUM” and write in output sheet in “Inception Premium” and the same column “TypeOf” contains Endorsement then add alll the amount in the column “TOTAL_PREMIUM” and write in Output Sheet “TOT_ENDORSEMENT_PREMIUM”

6

@ISBS_ROBOT

  1. If Condition Error: Convert variables to strings for comparison like this:

If Row2(“policy number”).ToString() = PolicyNumber.ToString() Then
’ Your code here
End If

  1. Assign Activity Error: Cast variables to appropriate numeric types before addition:

Assign MergedPremium = CDbl(InceptionPremium) + CDbl(TotalPremium)

Make sure to handle potential exceptions and null values as needed.

7

@ISBS_ROBOT

try this Xaml its working for me

Xaml : - Excel duplicates and sum the amount.zip (2.5 KB)

let me know its working or not

@ISBS_ROBOT

8

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