Grey Walker Grey Walker - 1 year ago 135 Question

Increment column value in datatable if duplicate row is found

I am trying to summarize some data in a data table, what I am attempting to do is to sum the total quantity of duplicate rows found.

My data table looks like this.

|Dave |Smith | 10000 |
|Dave |Smith | 20000 |
|Dave |Smith | 30000 |
|John |Peacock| 10000 |

I want to summarize this data to look like this.

|Dave |Smith | 60000 |
|John |Peacock| 10000 |

At the moment I am searching for duplicates in the data table

Dim duplicates = From rows In dt.AsEnumerable().GroupBy(Function(r) New With {Key .a = r("ForeName"), Key .b = r("SurName")}).Where(Function(gr) gr.Count() > 1).ToList()

However where to proceed from here I am unsure, has anyone ever came across a scenario like this and able to point me in the right direction.

Answer Source

Group the rows by name(s) or ID; use the name/ID as the identifier in a new anon type, and a Count/Total or Quantity property to collect the Sum of the Quantity:

Dim duplicates = myDT.AsEnumerable().
                 GroupBy(Function(r) New With {Key .Item = r.Field(Of String)("Name")}).
                 Select(Function(g) New With {Key .Name = g.Key.Item,
                                              Key .Count = g.Count,
                           Key .Total = g.Sum(Function(s) s.Field(Of Int32)("Quantity"))}).
                                      OrderByDescending(Function(j) j.Total).

For "fun" it also counts the number of source/group rows and orders them by the total.

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download