Aggregating Total Invoices for Duplicated Customer Orders in different sheets

[sasadrinksprite]

Hello everyone,
I’m facing an Excel-related issue in calculating columns in different sheets.

I would greatly appreciate any assistance and Thank you to anyone who can help!

[mkankatala (Mahesh Kankatala)]

Hi @sasadrinksprite

→ Use the read range workbook activity to read the Order Data sheet and store in a Datatable called dt1.
→ Use another read range workbook activity to read the Customer Invoice sheet and store in a datatable called dt2.
→ Take an assign activity and write the below linq expression

- Assign -> dt2 = (From row1 In dt1.AsEnumerable()
                   Join row2 In dt2.AsEnumerable() On row1.Field(Of String)("Customer Name") Equals row2.Field(Of String)("Customer Name")
                   Group row1 By CustomerName = row1.Field(Of String)("Customer Name") Into Group
                   Select dt2.Rows.Add(CustomerName, Group.Sum(Function(row) row.Field(Of Double)("Total Price")))).CopyToDataTable()

→ Use write range workbook activity to write the dt2 to the Customer Invoice Sheet.

Check the below workflow for better understanding,

Input -
Order Data Sheet -

Customer Invoice Sheet -

Output -

Hope it helps!!

[ppr (Peter Preuss)]

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode th…

describes Non-LINQ / LINQ Approaches for your case