Group the information in Excel

Help
,
7

Like this?
(remember to set the path to wherever you put the Excel).
DT_GroupCalculateTest.xaml (12.0 KB)
GroupingOutput.xlsx (8.2 KB)

Sometimes splitting actions into separate activities, while looking “longer” makes it easier to write and understand (also for long-term maintainability). In this case, doing it per step (1. Group values; 2. Calculate totals; 3. Prepare new outputs; 4. Write the outputs) is much clearer than trying to write a one-liner that does it all.

For those that don’t like to download to see (sorry for the mixed syntax of C#/VB.Net :wink: )

DataTable sampleDT = ReadRange(someExcelPath);
IEnumerable(Of IGrouping(Of Object, DataRow)) groupings = sampleDT.AsEnumerable().GroupBy(Function(x As DataRow) x.Item("TranType"));
foreach (var grouping in groupings)
{
	DataTable tempDT = grouping.AsEnumerable().CopyToDataTable();
	DataRow newRow = tempDT.NewRow();
	newRow("TotalCount") = grouping.Sum(Function(x) CInt(x.Item("TotalCount")));
	newRow("TotalAmount") = grouping.Sum(Function(x) CDec(x.Item("TotalAmount")));
	tempDT.AddDataRow(newRow);
	WriteRange(Path: someExcelPath, DataTable: tempDT, SheetName: grouping.Key.ToString);
}
2 Likes