Summing multiple columns based upon unique value

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

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

we can do it with grouping the data

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")))

image

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

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.

1 Like

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

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