Summing multiple columns based upon unique value

[Chris_Bolin]

I think I am at the point where I am chasing a rabbit hole. I know this is easy but I am drawing a blank. I have a data table such as this:

and as you can see there are multiple, same numbers in column A. Based upon a unique value in A, I need to determine a sum of values for Quantity then a sum of values for Line Item Value, etc.

Anyone want to take stab and not laugh because I am making this more complicated than it is?

Chris

[Chris_Bolin]

Just a note - I am not summing the entire table but just the values for each unique entry number. I tried filter table and it is not working

[ppr (Peter Preuss)]

we can do it with grouping the data

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

[ignasi.peiris]

Good afternoon @Chris_Bolin !

You can try this approach:

1- Find all unique values on your “EntryNumber” Column with the following LINQ:

var_dt.AsEnumerable.Select(function(x) x("EntryNumber")).Distinct

• Where var_dt is your DataTable, and “EntryNumber” is your column name.

The output of that “Distinct” will be a IEnumerable of Objects, save it into that variable:

Then with a Filter LINQ + a Sum LINQ you will have your desired output:

Filter:

var_dt.AsEnumerable.where(function(x) x("YourColumnName").ToString.Equals(UniqueValue.ToString)).CopyToDataTable

Sum:

var_FilteredDT.AsEnumerable.Sum(function(x) Cdbl(x("YourQuantityColumnName")))

var_Sum will contain your Sum based on every unique value on Column 1.

Example code with a custom excel:
ColumnSum.zip (62.0 KB)

Hope it helps!
Ignasi

[Chris_Bolin]

Thanks Ignasi

I previously tried your solution using LinQ as well. Already have the distinct items. The problem I am particularly running into is that when adding the data rows to a cloned DT it is generating a duplicate line for each line in the loop (There are several thousand distinct items each month). I eventually just threw in a remove duplicate lines from the cloned Dt and it is fine. Takes a little longer than I wanted but it is what it is.

[Chris_Bolin]

Grouping seems ideal if we are only working with one column that needs to be aggregated. I have several columns that need aggregated hence why I was trying for a different solution with LinQ

[ppr (Peter Preuss)]

Grouping also can be done with more then 1 columns e.g.

From d in dtData.AsEnumerable
Group d by k1=d(…), k2=d(…) … into grp=Group
…

Also the Non-LINQ Approach of filtering the distinct values can be applied for more than 1 column.

Processing the group members can also be done for more then 1 columns

We would suggest rereading the provided link:
[HowTo] Overview on different options for grouping data and processing the groups

also, feel free to share sample data and a very clear and sharp formulated requirement description including the expected output description