How to merge the duplicate values and unique values of same duplicate no


I am getting stuck when trying to merge duplicate rows and also trying to get the unique values of duplicate rows and unique rows

here is an example of what I am trying to get:



Any ideas?

Hi @smarthari1997

You can try using the group by statement as below in Assign

dtout = (From d In Dtin.AsEnumerable()
Group d By k=d("Ref").toString.Trim, k1=d("Bill").toString.Trim Into grp = Group
Let Amt = grp.Sum(function(x) CDBL(x("Amount").ToString.Trim))
Let dates = String.Join(",",grp.Select(function(x) x("Date").ToString))
Let ra = New Object(){k,k1,coun}
Select r = Dtout.Rows.Add(ra)).CopyToDataTable()

Dtin is the data you read from excel
DTOut is the data you get after grouping data with Ref and Bill and then get sum and concatenated dates

Use build datatable to give a 4 column structure to DTout

Let me know if you need any help.



How about the following?

dt =dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Ref").ToString,r("Bill").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) CDbl(r("Amount"))),String.Join(",",g.Select(Function(r) r("Date").ToString))},False)).OrderBy(Function(r) r("Ref").ToString).CopyToDataTable (2.8 KB)



The date is appearing like this 11/11/2022 00:00:00,11/12/2022 00:00:00 but i need only date

Hi @smarthari1997

I have update the yoichi expression. Can you check out this?

dt =dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Ref").ToString,r("Bill").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) CDbl(r("Amount"))),String.Join(",",g.Select(Function(r) Cdate(r("Date").ToString("MM-dd-yyyy"))))},False)).OrderBy(Function(r) r("Ref").ToString).CopyToDataTable



In place of x("Date").ToString() please use x("Date").ToString.Trim.Split({" "},stringsplitoptions.RemoveEmptyEntries)(0)


1 Like

can you explain about that code? i cant get it!!


Here is the break down

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Ref").ToString,r("Bill").ToString))

This will group the datatable rows on Ref and Bill and give groups of data

Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) CDbl(r("Amount"))),String.Join(",",g.Select(Function(r) r("Date").ToString))},False))

(For each group)Now from the groups we are consolidating and selecting a row which contains items as
g.Key.Item1 - ‘Ref’ Column value
g.Key.Item2 - ‘Bill’ Column value
g.Sum(Function(r) CDbl(r("Amount"))) - In the group sum of the ‘Amount’
String.Join(",",g.Select(Function(r) r("Date").ToString)) - In the group ‘Date’ Column joined with comma(,)

False - Is to just say that not to accept the changes or not to add to dt instead create and return with structure of dt

OrderBy(Function(r) r("Ref").ToString).CopyToDataTable

This will order the returned rows on Ref column and then copy the data to datatable

Hope this helps


can you make clear about the false please?


For loaddatarow… if we want to load the data into dt then we give as true …when we only want to replicate a action like load but actually do not make any addition to the dt then we use false…

In this case…loaddatarow is used to create a row from the groups retrieved but we dont need it to be added to dt…we only want to select a row from the dt


1 Like

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