I have creat a datatable including cloumn as name, time, type,money. I want to group the infromation by name, time, frist(Type),sum(the amount). Can I use “Assign” to do this? I have something wrong with my program. (As Attachment)Main.xaml (11.6 KB)
Hi , I have the same problem . "datable.Select.AsEnumerable().GroupBy(Function(r) r.Field(Of String)(“name”)).[Select](Function(g) g.First()).CopyToDataTable() " the result is not correct. Do you have some handbook or other infromation which can tell me : how to use the function or formula ? thanks
we should group by the datatable column “name”
what if I need all of them but not the first item, from this,
datable.Select.AsEnumerable().GroupBy(Function(r) r.Field(Of String)(“name”)).[Select](function(g) g.First()).CopyToDataTable()
Thanks.!
what’s your criteria? Please provide a sample.
Guys @vvaidya @aksh1yadav @akhi_s27 @Vikas.Jain @badita @beesheep @Florent_Salendres ,
I have a big data table and want this to be grouped by some column and finally makes it small having any columns summing its totals too…
You can take the same sample from initial post or the workflow you used… but it should group and sum any total columns.
This is what I need to achieve for different GroupBy TranType column.
at last I want to capture for each TranType —> TranType, Total Count, Total Amount, for example as per Images,
TranType, Total Count, Total Amount
Payment, 35, 270430.55
Reserve, 11836, 11625308.03
Recovery, 560, 980862.46
Recovery Reserve, 2613, 2052199.44
Hope its clear now…
Thanks!
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 
)
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
Doskonały
Thanks @andrzej.kniola.
If there is a similar datatable, which is target that differs only with Total Count and Total Amount column values, In such case all that needs is difference of these columns with rest elements captured as below screenshot…
How to get that the best way ?
Thanks for your time and sharing the knowledge…
Regards!
I consult you there will be another way that the result grouping and totals is in an excel sheet and not in several excel sheets. Thank you
@halvarez
I don’t quite understand the question, tbh.
@Dilli
Sorry, didn’t see your follow up. Is this still open (probably not, after 7 months)?
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);
}
taking into account this exercise to group the information in excel I ask the following:
The result of groupings and totals is written in several sheets. What will be the solution that writes groupings and totals a page? Thank you
@halvarez just move the below line after your for each step that should resolve your problem.
WriteRange(Path: someExcelPath, DataTable: tempDT, SheetName: grouping.Key.ToString);
Good morning friend, what a pity, I have a question and what would be the solution:
in the grouping activity:
DTleer.AsEnumerable (). GroupBy (Function (x As DataRow) x.Item (“SELLER”)) I modified it as a parameter for arguments:
DTleer.AsEnumerable (). GroupBy (Function (x As DataRow) x.Item (in_NombreHead)) when I made the modification I do not accept it. I attach my activity to find a solution thousand thanks for the collaboration.Group Excel.zip (450.2 KB)
Good morning friend, what a pity, I have a question and what would be the solution:
in the grouping activity:
DTleer.AsEnumerable (). GroupBy (Function (x As DataRow) x.Item (“SELLER”)) I modified it as a parameter for arguments:
DTleer.AsEnumerable (). GroupBy (Function (x As DataRow) x.Item (in_NombreHead)) when I made the modification I do not accept it. I attach my activity to find a solution thousand thanks for the collaboration.Group Excel.zip (450.2 KB)
Good morning friend, what a pity so much trouble, I have a question and what would be the solution to what I just sent. thank you
Good morning friend, what a pity, I have a question and what would be the solution:
in the grouping activity:
DTleer.AsEnumerable (). GroupBy (Function (x As DataRow) x.Item (“SELLER”)) I modified it as a parameter for arguments:
DTleer.AsEnumerable (). GroupBy (Function (x As DataRow) x.Item (in_NombreHead)) when I made the modification I do not accept it. I attach my activity to find a solution thousand thanks for the collaborationGroup Excel.zip (450.2 KB)
ashokkarale
MD_Farhan1
Ajay_Mishra
postwick
Dheerendra_vishwakarma
Anil_G
chandreshsinh.jadeja
Gautham_Pattabiraman
vrdabberu
aravindbalineni123