# Datatable summing of above rows based on condition

Hi All,
I have an excel sheet with 83+ columns and out of these I need to work on few columns create a data , here is the input

Layer Type Company Value
1 T a 100
1 F b 200
2 T c 300
2 F d 400
3 F e 500
3 F f 600
The expexted output is:
Output
Layer Type Company Value Combined
1 T a 100
1 F b 200 300 XS of 100
2 T c 800
2 F d 400 1200 XS of 300
3 F e 500 1100 XS of 1500
3 F f 600 1101 XS of 1500
So here I need to consider the layers and Type columns for my decesion to sum up the value column and create a combined data colum. My logic is that I want to sum the current layers in XS of the above rows. For eg for layer 2 (400+800) in XS of (200+100)
For Layer 1 (200+100)in XS of 100 , for the Type as T I donot need to have any combine data row I need to consider its value , For Layer 3 (600+500) in XS of (400+800+200+100)

Please if any one of u can suggest something , I tried by for loop bt its not working fine

@Yoichi , Please any help from your end. looks like a groupby case. An introduction to this (NON-LINQ, LINQ Approaches) find here:

Hi,

I think the first combined value should be “300 XS of 0”. If it’s wrong, can you share the way of calculation?

``````Dim t As Int32 =0
dt.AsEnumerable.GroupBy(Function(r) r("Layer").ToString).ToList.ForEach(
Sub(g)
Dim s As Int32 = g.Sum(Function(r) CInt(r("Value").ToString))
g.Tolist.ForEach(
Sub(r)
If (r("Type").ToString="F") Then
r("Combined")= s.ToString+" XS of "+t.ToString
End If
End Sub
)
t=t+s
End Sub
)
``````

Regards,

Hi Yoichi,

For the first layer the value in XS of will be the first value of the row with type T i.e 100

The code file that you have shared is not openening can you please share it again.

Hi,

``````Dim t As Int32 =CInt(dt.Rows(0)("Value").ToString)
Dim isFirst As Boolean = True
dt.AsEnumerable.GroupBy(Function(r) r("Layer").ToString).ToList.ForEach(
Sub(g)
Dim s As Int32 = g.Sum(Function(r) CInt(r("Value").ToString))
g.Tolist.ForEach(
Sub(r)
If (r("Type").ToString="F") Then
r("Combined")= s.ToString+" XS of "+t.ToString
End If
End Sub
)
If (isFirst) Then
t=s
Else
t=t+s
End If
End Sub
)
``````

Sample20230220-1aL.zip (2.9 KB)

Regards,

Thank you so much , I will try this and get back!!

This is not returning the correct sum of above layers

HI,

Sorry,I forgot to change flag value. How about the following? Is this result correct?

``````If (isFirst) Then
t=s
isFirst = False
Else
t=t+s
End If
``````

Sample20230220-1aLv2.zip (2.9 KB)

Regards,

Thank you 1 Like

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