Excel Automation for duplicate values

[Ram_Shiva_Reddy]

Dear Team,

In excel file, there were two columns. One with many duplicate values and other with the respective amount. I need to merge the one value to sum the amount. Please help me with this.

[Gokul001 (Gokul Balaji)]

HI @Ram_Shiva_Reddy

Can you share the sample Input and Output excel file?

[Ram_Shiva_Reddy]

Revised 10038 WPs FTO wise final data (Autosaved).xlsx (522.3 KB)

This is the file Sir

[Gokul001 (Gokul Balaji)]

Hi @Ram_Shiva_Reddy

How about this expression?

(From d In DtBuild.AsEnumerable
Group d By k=d("Column 1").toString.Trim Into grp = Group
Let nj = grp.Sum(Function (x) CDbl(x("Column 2").toString.Trim))
Let ra = New Object(){k,nj}
Select r = DtOutput.Rows.Add(ra)).CopyToDataTable

Regards
Gokul

[Ram_Shiva_Reddy]

I tried Sir, but its not working.
in the duplicate values, need to merge the value and sum the amount

[Gokul001 (Gokul Balaji)]

Hi @Ram_Shiva_Reddy

Your excel file is not good, It has - and some value like in the screenshot.

Try with this expression

(From d In DtBuild.AsEnumerable
Group d By k=d("W.P.No").toString.Trim Into grp = Group
Let nj = (From d In grp.AsEnumerable Where Not (isNothing(d(1)) OrElse String.IsNullorEmpty(d(1).toString.Trim) OrElse d(1).tostring.Contains("-")) Select v = CDbl(d(1).toString.Trim)).Sum(Function (x) x)
Let ra = New Object(){k,nj}
Select r = DtClone.Rows.Add(ra)).CopyToDataTable

Regards
Gokul

[Ram_Shiva_Reddy]

@Gokul001

(From d In DtBuild.AsEnumerable
Group d By k=d("W.P.No").toString.Trim Into grp = Group
Let nj = (From d In grp.AsEnumerable Where Not (isNothing(d(1)) OrElse String.IsNullorEmpty(d(1).toString.Trim) OrElse d(1).tostring.Contains("-")) Select v = CDbl(d(1).toString.Trim)).Sum(Function (x) x)
Let ra = New Object(){k,nj}
Select r = DtClone.Rows.Add(ra)).CopyToDataTable

Yes sir,
Thats the issue. Is there any other possible solution. please let me know

[Gokul001 (Gokul Balaji)]

HI @Ram_Shiva_Reddy

How about this LINQ

(From d In DtBuild.AsEnumerable
Group d By k=d("W.P.No").toString.Trim Into grp = Group
Let nj = (From d In grp.AsEnumerable Where Not (isNothing(d(1)) OrElse String.IsNullorEmpty(d(1).toString.Trim) OrElse d(1).tostring.Contains("-") OrElse String.IsNullOrWhiteSpace(d(1).tostring.trim)) Select v = CDbl(System.Text.RegularExpressions.Regex.Replace(d(1).Tostring.Trim," ","").Tostring)).Sum(Function (x) x)
Let ra = New Object(){k,nj}
Select r = DtClone.Rows.Add(ra)).CopyToDataTable

Check out the XAML file

GroupBySumLINQ.xaml (12.6 KB)

Output

Revised 10038 WPs FTO wise final data (Autosaved).xlsx (508.6 KB)

Regards
Gokul