Duplicates from one sheet and adding

[ramshiva_reddy]

Dear Team,

I’m facing an issue with excel task. Need sheet2 as an expected output format… Please help me with this.
Team Training - Assignment 3.xlsx (11.5 KB)

[arjunshenoy]

Hi @ramshiva_reddy

Based on the provided data in the sheet2 & expected output sheet, you can use the following query for grouping the data:

outputTable = (From row In inputTable.AsEnumerable()
               Group row By Fruits = row("Fruits") Into Group
               Select New With {
                   .Fruits = Fruits,
                   .Quantity = Group.Sum(Function(r) CInt(r("Quantity")))
               }).CopyToDataTable()

Hope this helps,
Best Regards.

[Yoichi]

HI,

How about the following?

dt = dt.AsEnumerable.GroupBy(Function(r) r("Fruits").ToString()).Select(Function(g) dt.Clone.LoadDataRow({g.Key,g.Sum(Function(r) CInt(r("Quantity").ToString()))},False)).CopyToDataTable()

Sample20230710-2L.zip (11.2 KB)

Regards,

[ramshiva_reddy]

Bro,
Not through link query. Is it possible in another way

[arjunshenoy]

@ramshiva_reddy

Well, in that case, how about the following approach?

outputTable = inputTable.Clone()
For each row in inputTable
    foundRows = outputTable.Select("Fruits = '" + row("Fruits").ToString + "'")
    
If foundRows.Count > 0
        foundRows(0)("Quantity") = CInt(foundRows(0)("Quantity")) + CInt(row("Quantity"))

Else  newRow = outputTable.NewRow()
        newRow("Fruits") = row("Fruits")
        newRow("Quantity") = row("Quantity")
        outputTable.Rows.Add(newRow)
    
End For Each

Hope this helps,
Best Regards.

[Yoichi]

@ramshiva_reddy

Not through link query. Is it possible in another way

FYI, another approach using Dictionary.

Sample20230710-2Lv2.zip (11.7 KB)

[ramshiva_reddy]

Hello,
I’m getting an error at 2nd for each activity.

[Yoichi]

Hi,

Did you run the sample as it is or modified workflow?
What error do you have?

Regards,

[ramshiva_reddy]

Hello,
I have attached image. Please check

[Yoichi]

HI,

Can you try to add new object() as the following because of Windows project?

 new object() {currentItem.Key,currentItem.Value}

Regards,

[ramshiva_reddy]

Thank you. Got the solution.