Find duplicates in data table, calculate value

Help Activities
,
1

Hello community,

i have the following data scheme in a datatable:
ID; Value
A123; 10
B456; 20
C789; 30
C789, 40

What I want to have is one entry per ID (without duplicate ID entries) with the sum of the values corresponding to the ID.
For the example above I want to have the following output:

ID; Value
A123; 10
B456; 20
C789; 70

What would be the best approach to get this done?

Kind regards
Joseph

2

Use this query to get Distinct Values

Your_DT.AsEnumerable().GroupBy(Function(i) i.Field(Of String)(“Value”)).Select(Function(g) g.First).CopyToDataTable()

2 Likes
3

@JoSom
Welcome to the forum

we can match it to a group by case and sum up the members

Assign Activity:
dtResult = dtorig.Clone

Assign Activity:
dtResult =

(From d in dtOrig.AsEnumerable
Group d by k=d("ID").toString.Trim into grp=Group
Let sm = grp.Sum(Function (x) CInt(x("Value").toString.Trim))
Let ra = new Object(){k,sm}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable
4

DT=DT.asenumerable.Groupby(Function(r) r(“ID”).Tostring).Select(Function(g) g.First()).Copytodatatable

5

Hi ppr,

thanks for the quick help - your solution works perfect!
As I am pretty new to LINQ, how would you add second column (Date) which corresponds to the ID to the output datatable?

Input data would look like this:
ID; Value; Date
A123; 10; 01.01.2022
B456; 20; 01.02.2022
C789; 30; 01.03.2022
C789, 40; 01.03.2022

I read your how-to-post but still I am not able to get it right - I would really appreciate your support.

Kind regards
Joseph

6

Assumption: Col Structure: ID; Value; Date

Assign Activity:
dtResult = dtorig.Clone

Assign Activity:
dtResult =

(From d in dtOrig.AsEnumerable
Group d by k=d("ID").toString.Trim into grp=Group
Let sm = grp.Sum(Function (x) CInt(x("Value").toString.Trim))
Let ra = new Object(){k,sm, grp.First()(2)}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

Kindly note:

  • As date can also occur within a group with different values. The implementation is here coded in order to to take the first groupmember row and its date

there are several strategies in how to construct the itemarray and depends on the details of the col structure to take best minimal effort fitting approach

No problem, on the begin of the howto also a NON-Linq approach is described

7

Thanks - your input really helped!

8

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