Linq sum from the excel

Help Studio
, , , ,
1

Hello everyone.
I got an excel like this
Name Col1 Col2
Apple 10 15
Orange 15 20
Apple1 2 3
Orange1 8 10

And the output I want is
Name Col1 Col2
Apple 8 12
Orange 7 10
Apple1 2 3
Orange1 8 10
like Apple = Apple-Apple1 , Orange=Orange-Orange1
Is it possible with Linq or smth?

1 Like
2

Hi @Temuulen_Buyangerel

What is your required output. Share some details how it looks like.

4

New Microsoft Excel Worksheet.xlsx (8.6 KB)

5

Hi,

Try as below:-

dt = (From row In dt.AsEnumerable()
Let commonPart = System.Text.RegularExpressions.Regex.Match(row(“Name”).ToString(), " [1]+ ").Value
Group row By commonPart Into Group
Let totalCol1 = Group.Sum(Function(r) Convert.ToInt32(r(“Col1”)))
Let totalCol2 = Group.Sum(Function(r) Convert.ToInt32(r(“Col2”)))
Select dt.Rows.Add(commonPart, totalCol1, totalCol2)).CopyToDataTable()

This LINQ statement groups the rows by the common part of the “Name” column, calculates the sum of “Col1” and “Col2” for each group, and then creates a new DataTable with the updated values.

Thanks

  1. A-Za-z ↩︎

6

Hey, thanks for reply but I think it’s not the output that I want.

7

Hi,

How about the following sample?

image
image799×482 24 KB

dt.AsEnumerable.GroupBy(Function(r) System.Text.RegularExpressions.Regex.Match(r("Name").ToString,"[A-Za-z]+").Value).SelectMany(Function(g) g.Select(Function(r) dt.Clone.LoadDataRow({r("Name").ToString,CInt(r("Col1"))-if(System.Text.RegularExpressions.Regex.IsMatch(r("Name").ToString,"\d+$"),0,g.Where(Function(r2) System.Text.RegularExpressions.Regex.IsMatch(r2("Name").ToString,"\d+$")).Sum(Function(r2) CInt(r2("Col1")))),CInt(r("Col2"))-if(System.Text.RegularExpressions.Regex.IsMatch(r("Name").ToString,"\d+$"),0,g.Where(Function(r2) System.Text.RegularExpressions.Regex.IsMatch(r2("Name").ToString,"\d+$")).Sum(Function(r2) CInt(r2("Col2"))))},False))).CopyToDataTable()

Sample
Sample20240315-2.zip (9.0 KB)

Regards,

1 Like
8

Thank you!
It works
How about its for every column instead of only Col1,Col2?

9

How about its for every column instead of only Col1,Col2?

The following part is for Col2. So if you need to add other column, please replace Col2 with the column name then add it after the col2 part with comma.

CInt(r("Col2"))-if(System.Text.RegularExpressions.Regex.IsMatch(r("Name").ToString,"\d+$"),0,g.Where(Function(r2) System.Text.RegularExpressions.Regex.IsMatch(r2("Name").ToString,"\d+$")).Sum(Function(r2) CInt(r2("Col2"))))

If there are many columns to calculate it, it may be better to use LINQ for it.

Regards,

1 Like
10

Thank you!
I appreciate it

1 Like
11

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