How to sum all duplicates in a column

Help
1

Hi. I’ve got an excel sheet containing two columns, ID and Amount. I want to read this file in the robot, create a dt (let’s call it dt1), loop through the dt, sum all duplicates and assign the sum of each duplicate to the duplicate rows, then remove the duplicates and output the resulting dt as dt2. Any input/workflows that will allow this?

2

Hi @rlohne,

Refer this below post to get the duplicate data from the data table.
DataTable filtering with expressions

After getting that data table you can sum it.

Regards,
Arivu

3

Thank you @arivu96, but I’m afraid I’m new to this, so the post doesn’t really help me understand how to achieve what I want.

4

@arivu96 how do I choose which column to select the duplicates based on? I’m using the following code from your post:

(From p in dt.Select() where( From q in dt.Select() where string.Join(“,”,q.ItemArray).Equals(string.Join(“,”,p.ItemArray)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

I’d like to select based on duplicates in Column B, named “Numbers”.

5

Figured it out in the end:

dt.AsEnumerable.Where(Function(x) x(“Column”).ToString.Trim.Equals(variable)).Sum(Function(x) Convert.ToDouble(x(“Column”).ToString.Trim) ).ToString

Is the code for summing all duplicates and

dtAsEnumerable().GroupBy(Function(i) i.Field(Of String)(“Column”)).Select(Function(g) g.First).CopyToDataTable

is for removing duplicates.

2 Likes
6

Hi,

How am I suppose to use the summing all duplicates code? Do I use an assign activity?

Thanks
Vasundhara

7

You can use expressions like that in any field that supports the type of value it is. Since it has .ToString, you can use that in any property (like TypeInto or Assign, etc) where you can use a string. If you take off the .ToString, then the Sum is a Double type, so you can use that in calculations if you would like.

So, if you want to store that value in a variable or row item, then yeah use an Assign activity.

For example:

Assign activity: row("Amount") = dt.AsEnumerable.Where(Function(x) x(“Column”).ToString.Trim.Equals(variable)).Sum(Function(x) Convert.ToDouble(x(“Column”).ToString.Trim) ).ToString

Regards.

2 Likes