Merging rows and sum

Help Studio
1

Hi,

Would like to seek help here to merge rows if column A and column B contains the same description and reference number, and sum the amount into 1 row.

Pls see illustration below

image
image1512×2016 2.02 MB

2

HI @flyingdragon

Checkout this thread

Regards
Sudharsan

3

Hi @Sudharsan_Ka

Am i missing something as i encountered error “Complier error encountered…”

(From d In dT_main.AsEnumerable
Group d By k1=d(0).ToString.Trim.Substring(0,10),k2=d(1).ToString.Trim Into grp=GroupDescription
Let s = grp.sum(Function (x) CDbl(x(2).ToString)
Let ra = New Object (){k1,k2,s}
Select dT_Main1.Rows.(ra)).CopytoDataTable

4

Hi @flyingdragon ,

It looks like a Typo Error on your part. Could you change GroupDescription to Group and Check.

If still receiving errors, provide us with the Screenshots of your implementation to better understand.

5

@flyingdragon

(From d In dT_main.AsEnumerable
Group d By k1=d(0).ToString.Trim,k2=d(1).ToString.Trim Into grp=Group
Let s = grp.sum(Function (x) CDbl(x(2).ToString)
Let ra = New Object (){k1,k2,s}
Select dT_Main1.Rows.(ra)).CopytoDataTable
6

And make a note you need two datatable @flyingdragon

dT_main → Main source of datatable with the data

dT_Main1 → The Datatable only with the headers of the main source in this datatable the data will be get added so you need to use this datatable to write in the excel

Regards
Sudharsan

7

Still having the same error. Yes dt_main is my main source and dt_main1 is datatable with headers only

image
image3945×1420 876 KB

8

Check this @flyingdragon

(From d In dT_main.AsEnumerable
Group d By k1=d(0).ToString.Trim,k2=d(1).ToString.Trim Into grp=Group
Let s = grp.sum(Function (x) CDbl(x(2).ToString))
Let ra = New Object (){k1,k2,s}
Select dT_Main1.Rows.(ra)).CopytoDataTable
9

Same error with identifier expected

10

@flyingdragon

(From d In dT_main.AsEnumerable
Group d By k1=d(0).ToString.Trim,k2=d(1).ToString.Trim Into grp=Group
Let s = grp.sum(Function (x) CDbl(x(2).ToString))
Let ra = New Object (){k1,k2,s}
Select dT_Main1.Rows.add(ra)).CopytoDataTable
11

As you have $ in your data updated one @flyingdragon check with this

(From d In dT_main.AsEnumerable
Group d By k1=d(0).ToString.Trim,k2=d(1).ToString.Trim Into grp=Group
Let s = grp.sum(Function (x) CDbl(System.Text.RegularExpressions.Regex.Match(x(2).ToString.Trim,"\d.+").ToString.Trim))
Let ra = New Object (){k1,k2,s}
Select dT_Main1.Rows.add(ra)).CopytoDataTable
12

Thanks! The error is now gone but when i run the file, there’s another error "Conversion from string “” to type “Double” is not valid.

13

I managed to use this code for my work. Thanks @Sudharsan_Ka for the help!

(From d In dT_main.AsEnumerable
Group d By k1=d(0).ToString.Trim,k2=d(1).ToString.Trim Into grp=Group
Let s = grp.sum(Function (x) CDbl(x(2).ToString))
Let ra = New Object (){k1,k2,s}
Select dT_Main1.Rows.add(ra)).CopytoDataTable

14

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