[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.


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:

A possible scenario could be:

  • Create a report containing following information:
    • the region code
    • the sum of CaseCount per RegionCode
    • the list of CaseNames per RegionCode

In order to get such a report we can define:

  • Grouping Criteria: The value of the Column RegionCode
  • Aggregation for the CaseCount: Summing up CaseCount values
  • Aggregation for the CaseNames: Concat the CaseName along with a seperating “;”

Grouping the Data

Approach: Filtering on distinct Group Criteria Values


  • Retrieval of the distinct Group Criteria Values
  • Filter the datatable based on the distinct Group Criteria Values

For getting the distinct RegionCode Values the datatable.DefaultView can help:
RegionCodes = dtData.DefaultView.ToTable(True, {"RegionCode"})


Based on the unique RegionCodes the datatable can be filtered and the different groups are retrieved:


Approach: LINQ - Group By

For retrieving the Grouped data a LINQ can be used:

Option 1: Returning the grouped data as a List(Of List(Of Datarow))

  • outer List: Groups, inner List: group members:
  • grafik
(From d In dtData.AsEnumerable
Group d By k=d("RegionCode").toString.Trim Into grp=Group 
Select grp.toList).toList


Option 2: Returning the grouped data as a List(Of List(Of DataTable):

(From d In dtData.AsEnumerable
Group d By k=d("RegionCode").toString.Trim Into grp=Group 
Select grp.CopyToDataTable).toList


Processing the grouped Data

Option 1: Iteration of the groups

Input: DataTable.DefaultView Approach

  • Retrieval of the distinct Group Criteria Values - RegionCode | DataTable
  • For each row: Loop over RegionCode - TypeArgument: DataTable
    • Filter the datatable based on the distinct Group Criteria Values - tmpGroupDT
    • For each row: Loop over tmpGroupDT | DataTable
      • Process the group member rows


  • the CaseCounts are summed up
  • the CaseNames are concatenated and seperated by a comma


Option 2: The LINQ Approach:

  • Ensure that an empty Datatable for the report is setup with the required DataColumns:
    • dtReport2 | DataType: DataTable:
  • Assign Activity with the LINQ statement
(From d In dtData.AsEnumerable
Group d By k=d("RegionCode").toString.Trim Into grp=Group 
Let cs = grp.Sum(Function (rc) CInt( "0" & rc("CaseCount").toString))
Let cn = String.Join(";", grp.Select(Function (rn) rn("CaseName").toString).toArray)
Let ra = New Object(){k, cs, cn}
Select dtReport2.Rows.Add(ra)).CopyToDataTable
Explanation of the LINQ and Visuals


  • Line1: Loop over dtData - d is referencing the current looped datarow
  • Line2: Group Data by the k (the RegionCode) into a group - grp is referencing the current looped group
  • Line3: Memorize the sum of CaseCount for the current looped group - referenced by cs
  • Line4: Memorize the concatenated CaseNames for the current looped group - referenced by cn
  • Line5: Construct the ItemArray which will be the report line for this for the current looped group - referenced by ra
  • Line6: Use ra for adding a datarow to dtReport2 and copy the result to the DataTable



Descriptions will be included soon

  • the Dictionary Approach
  • Decomposed LINQ
  • Activities from the marketplace


Find sample Project (done with 2020.10.4)
ppr_HowTo_GroupByOverview.zip (36.8 KB)

Starter Help XAML:
ppr_HowTo_GroupBy.xaml (20.7 KB)


LINQ Tutorials:

LINQ Details
linqsamples.com - GroupBy

.Net Api Reference: DataTable.DefaultView


For questions on your retrieval case open a new topic and get individual support

Sort datatable 3 times
Sort Data Table and Send consolidate email to the Sendder
Help with creating a Linq statement
Sum the values of a column when it meets a condition
Subset of a data from the data table for a specific Use Case
Concatenating rows
Send Excel Repot based on data from another excel
Group-by and calculate percentage
Delete duplicate rows from a datatable grouping 2 columns using LINQ
Implementing From For Each Row in Data Table code To Linq
Updating excel sheet for repeated row values
Excel read duplicate rows and compare columns data to those rows
Group by sportsvenue and get sum of each group
I need explaination of this linq query
Sort the Data in Excel and Create New Sheet with filtered Name
Linq Query, Please Help!
Help adding rows to each other in data table
Get Min and Max Value from datatable
How to do group by query using Linq
Group By Data Table on Date Range
Write x for each row which's column value is twice in data table
Need help in getting calculated values in a data table column without looping and grouping based on another column
Most efficient way to remove datatable duplicates while summing the dulpicates
Find Duplicates and Sum the values and update in Status Column
Summation of Hours per Employee
Given a DataTable, how do I merge similar row values?
Delete Duplicate Excel Rows Based On One Column
Data table Aggregation for dates
How to read the excel based on upper cell
Selecting specific rows where credit/debit balance does not equal zero
How to move values from row to row
How to group an excel data based on the name column and send each group data over email
Simplifying robot execution running time
Excel rows, create list only from integer numbers of rows
Excel need to filter date wise and separate the data according to customer name
What is means tolookup in linq
Group by duplicate ID and take the highest available amount from other columns
Linq Query on Group By and Sum
Trying to Group By DataTable based upon multiple columns
Get the last row index of particular duplicate value
Need to fetch multiple columns from a data table using Linq query
How to process group of records in data table once instead of individually?
If Excel Row matches append document number with comma
How to group a set of comments
Save Part From The Datatable Variable
How can I continue to lookup value from DataTable
Filter some data in excel file
Group column by name in excel
Need to fetch multiple columns from a data table using Linq query
Directory of directory
Data in Excel from Rows to Colums Below
How to group datatable values based on common criteria and manipulate them as a group
Selectively retrieving data from DataTable
Index of a value in Datatable
Read all data from excel and store into list
Return latest date from a duplicate row
Group Dates-By Week
Calculate Mode from a datatable tha got different values from the same name
Calculate Mode from a datatable tha got different values from the same name
Fill data in a column based on 2 condition
How do I do the Data Massaging as following in a data table?
[HowTo] LINQ (VB.Net) Learning Catalogue
Adding Sum of Specific Data In Excel
Datatable group multiple columns by list
How to get row count for each date present in Excel
How to group by multiple columns and keep all rows
DataTable Streamlining
How to count duplicate rows in a column of the same excel sheet and write it to the new excel file
Group By and Count with several conditions
How to count unique row value & insert into the same excel sheet?
How to do sum of column(Total profit) by Region in excel file
How can I remove duplicate data in datatable with using condition in columns
Split DataTable based on two unique values
Remove Dublicate Rows based on a column
Custom sort multiple column and delete rows without Excel
DataTable has same data but quantity column values are different, Sum the quantity column and make into single row
Picking row value dynamically with an additional action
How to concat common rows in excel file and and write into single cell
How to match data between 3 columns
Get unique rows and their count as a datatable
GroupBy on 1 Column - calculate the Average and Sums of the group members
How to do summation of row values, if one of the row has same value
DataTable - Process particular member from a group
Vlookup and remove duplicates
How to Restructure a Datatable to convert from Row to Row and Row Count (Group By Count Operation)?
Need To filter excel column by using LINQ query
Need a help on Group By
LINQ language
I need logic for this excel activity, I need to add all debit values of 1-Sep then for 2-sep I need add all the debit value belong to this date,,,So on
Check, Count and Delete duplicate items with summation
Linq Group by Sum
Count the repeated cells excel
Data sorting and filtering
How do I group similar data into separate files?
How can I seperate data and send the similar data in one email?
Can someone assist me how to extract the following data from excel
Withdraw amount from account
How to split a table in "n" tables each one containing a subset based on a single field of a record?
How to get all the values in a for each loop using group by on a specific column
I have to remove a row in datatable
For each loop in group wise
Summing multiple columns based upon unique value
Summing multiple columns based upon unique value
Add columns to datatable with LINQ
What is the best way to select a row with that a column consists a certain value in Excel
Join/ Merge the Rows into a single row in a data table
Mapping ColA to ColB values from Excel sheet
Datatable Add Column Values based on row values
Using LINQ to build table sum by staff no
Iterate through a datatable based on the specific condition of column
How to create two new datatables only with rows that have content?
Need to get multiple correspondent value using lookup function
How to compare two rows of same datatable and if it's found match then merge it
Sum duplicate values in Datable
Linq Query to combine multiple query
Select email addresses from datatable row neglecting duplicate
LINQ Query to find the first different instances of rows from DT
Segment and Group Datatable by not grouping item with same transaction ID/item name
Remove the duplicate and add the amount column
How to delete dublicate values in excel. based on 2 different columns dats
Group data table and sum values in different columns
Dictionary elements
Increase same valued data consecutively if occurs more than once
Remove duplicates in two columns and keep rest of all the column values
Split Csv file into multiple files according to month in date column
How to get the first occurrence for a word
Help on Linq query
Group By ID and merge
How to add values together from different datables
N number of Random Records per Unique Combination (Group/Key)
Pivot Table Creation12
How to split an excel file into 2 files based on a list,
How to merge duplicate rows and add values of other columns
Problem Filter Data Table not working
How can I understand this expression in read range activity

Hi @ppr

Nice :smile: Thanks… looking forward to updates on Alternates.

Thanks for your feedback.

Can u support in this, since iam not so familiar with LINQ

Thanks in advance

This is perfect but what if i want to group by more than 2 columns ?