Filter same column value and add up the values in another column(Linq Query)

Hi All,

I have a condition where

I need to check Column B and Column M.

If the column B values are same (Opportunity Names are exactly same) then I need to add up the Total value in Column M. Is there any Linq query for such conditions ?

For example,

in Column B AIA HK 1Yr Commit FY25 is same. I need to add up the corresponding values in column M(350,000+12,500+1000000)

Hi,

We can achieve it using LINQ GroupBy. Can you share your input data and expected result as file?

Regards,

@dutta.marina

You need only those two columns? if not then what should be the values of those two columns…

if only two are needed then build a new datatable with 2 columns string and Double

now newdt = dt.AsEnumerable.GroupBy(function(x) x(1).ToString).Select(functio(x) newdt.LoadDataRow({x.Key,x.Sum(function(y) CDBL(y(12).ToString))},False)).CopyToDataTable

cheers

Hi @Yoichi

I need to check if Oppty column has same value with same start date then add up those corresponding values in MFST commit column
MFST-Amount.xlsx (7.9 KB)

@Anil_G

Basically I need to check in Oppty Column B has same values with same start date in Column F, then add up those corresponding values in column M and get a single amount

@dutta.marina

as you need 3 create a tabe with 3 columns(2 string columns,1 double)

newdt = dt.AsEnumerable.GroupBy(function(x) x(1).ToString + x(5).ToString).Select(functio(x) newdt.LoadDataRow({x.First(function(y) y(1).ToString),x.First(function(y) y(5).ToString),x.Sum(function(y) CDBL(y(12).ToString))},False)).CopyToDataTable

cheers

@Anil_G

also will these filter any blanks. If I need to check column B(oPPTY NAME SHOULD NOT HAVE BLANKS ). Name should be there .

@dutta.marina

it would not…you can filter blank rows before running the query using filter datatable so that no blank rows are present

cheers

Hi,

How about the following?

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r(" Opportunity"),r("Start Date"))).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) CDbl(r("MSFT Commitment")))},False)).CopyToDataTable()

Sample
Sample20250203-4.zip (8.2 KB)

Regards,

@Yoichi

Also I need to filter any blank columns in Oppty column. Oppty column should not contain blanks.

@Anil_G

ok will do that

HI,

Can you try the following expression for the above sample?

dt.AsEnumerable.Where(Function(r) r(" Opportunity") isnot Nothing AndAlso not String.IsNullOrEmpty(r(" Opportunity").ToString)).GroupBy(Function(r) Tuple.Create(r(" Opportunity"),r("Start Date"))).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) CDbl(r("MSFT Commitment")))},False)).CopyToDataTable()
1 Like

You can achieve this using a LINQ query. Here is an example of how you can write the query to check if the values in Column B are the same and then add up the corresponding values in Column M: var result = dataTable.AsEnumerable() .GroupBy(row => row.Field(“ColumnB”)) .Select(group => new { OpportunityName = group.Key, TotalValue = group.Sum(row => row.Field(“ColumnM”)) }); This query groups the rows by the values in Column B and then sums the values in Column M for each group ​1​.

Bot said:

You can achieve this using a LINQ query. Here is an example of how you can write the query to check if the values in Column B

var result = dataTable.AsEnumerable()
.GroupBy(row => row.Field(“ColumnB”))
.Select(group => new
{
OpportunityName = group.Key,
TotalValue = group.Sum(row => row.Field(“ColumnM”))
});


This query groups the rows by the values in Column B and then sums the values in Column M for each group

@Anil_G

This should be my expected output. It should add up the amount, and three other columns should be present along with MSFT Commit column

Oppty, Start_date and Oppty url, MSFT Commit column

@dutta.marina

the query remains same just add what all columns you need inside loaddatarow

cheers

@Anil_G

Something like this

dt.AsEnumerable.GroupBy(function(x) x(“Opportunity”).ToString + x(“Start Date”).ToString +x(“Opportunity_url”).ToString).Select(functio(x) newdt.LoadDataRow({x.First(function(y) y(“Opportunity”).ToString),x.First(function(y) y(“Start Date”).ToString,x.First(function(y) y(“Opportunity_url”).ToString),x.Sum(function(y) CDBL(y(“MFST-Amount”).ToString))},False)).CopyToDataTable

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