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

1 Like

Hi Bro @ppr

Sorry for disturb.

As your solution, it write sum duplicate row only… but how to write another row ( not duplicate ) into datatable.

It will check, if rows are duplicate, it will sum…another still keep

If the datatable contains null value then it’s not working. What will be the query in that case?

I need to find duplicate in a column (eg branch account) and add the duplicates values in corresponding columns which may or may not contain null values. I am able to get all duplicate values bt sum is nt wrkng. Plz note the values to be added are decimal.
Plz suggest.