Excel Sum and deletion of duplicates

Hi all,

Assuming, we have an excel sheet with two columns, Client ID and Client expenditure. What I am trying to do is have robot access the spreadsheet and perform the following:

*Search for duplicate client id
*If a duplicate is found, then sum the overall client expenses

Any feedback on how this may be achieved?

Thank you very much,
Lilo

1 Like

Hi,
Please follow the below steps

  1. Excel Application scope with read range to read the excel
  2. Assign activity to assign dtTemp.Select(“ClientID= ‘yourVariable’”) to arrayofDaraRow variable
  3. check if count is greater than 1
  4. sum the expense column using the given code dtTemp.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(1).ToString.Trim) ).ToString here I have converted the input to double format, you can use integer format if there is no decimals present and I have stored in a string variable.

Please let us know if this was your solution.
Regards,
Pavan H.

hi @lilo
hope this will help you,
here’s my workflow:
groupBysum.xaml (11.8 KB)

dataTable:
dt1
O/P:
dtOp

2 Likes

You can try with (From r In dtData.AsEnumerable() Select C1 = r.Field(Of String)(“ClientID”), C2 = r.Field(Of Decimal)(“ClientExpenditure”) Group By C1 Into Group Select C1, C2 = Group.Sum(Function(x) x.C2)).ToArray()

1 Like

Hi,
I want to compare 2 columns to check duplicates.If it contains duplicate value then i need to sum 4 columns fields.
Do you have any idea

@Sob
Do you mind to put question in a new Topic and provide some sample data?

Hi,
For example

image
I need to find duplicate for ID and key column.if duplicates ID contains duplicate key.Then i need to sum Value1 ,Value 2 and value 3.
op will be like

image 82 xxx 10 125 220 0 20
test.xlsx (8.6 KB)

@Sob
For detecing the relevant values (Col ID, Col Key) of the duplicates I would suggest for:

  • Using an assign activity
    Statement:
    (From r In dtSample.AsEnumerable()
    Select C1 = r(0).ToString.Trim, C2 = r(2).ToString.Trim
    Group By C1, C2 Into Group
    Select C1, C2, Count = Group.Count
    Where Count > 1
    Select New String() {C1,C2}).ToList

returns a List of String()

The second part could be done in a classical way within a for each acitivty, taking care about the summing up (refer to some suggestions on above)

In case you need further help, please open a new Topic as your scenario is valuable for others and schould not get lost on the origin one

1 Like

Thanks.But i tried this method as well.This is not working properly.

Link to new Thread with solution