Skip to content

Instantly share code, notes, and snippets.

@karenpayneoregon
Created July 1, 2020 23:27
Show Gist options
  • Save karenpayneoregon/fd7ecfaf6e66307fe9277070ccc9b6ec to your computer and use it in GitHub Desktop.
Save karenpayneoregon/fd7ecfaf6e66307fe9277070ccc9b6ec to your computer and use it in GitHub Desktop.
Group by two DataColumns
Imports System.Text
''' <summary>
''' Requires
''' 1 Button named DistinctGroupButton
''' 1 DataGridView named DataGridView1
''' </summary>
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim dt As New DataTable With {.TableName = "MyTable"}
dt.Columns.Add(New DataColumn With {.ColumnName = "Identifier", .DataType = GetType(Integer),
.AutoIncrement = True, .AutoIncrementSeed = 1})
dt.Columns.Add(New DataColumn With {.ColumnName = "Priority", .DataType = GetType(String)})
dt.Columns.Add(New DataColumn With {.ColumnName = "Status", .DataType = GetType(String)})
dt.Rows.Add(New Object() {Nothing, "Low", "New"})
dt.Rows.Add(New Object() {Nothing, "High", "Old"})
dt.Rows.Add(New Object() {Nothing, "Low", "New"})
dt.Rows.Add(New Object() {Nothing, "Medium", "Old"})
dt.Rows.Add(New Object() {Nothing, "Low", "New"})
dt.Rows.Add(New Object() {Nothing, "Low", "New"})
dt.Rows.Add(New Object() {Nothing, "High", "Old"})
dt.Rows.Add(New Object() {Nothing, "Medium", "New"})
dt.Rows.Add(New Object() {Nothing, "Medium", "Old"})
dt.Rows.Add(New Object() {Nothing, "Low", "Old"})
DataGridView1.DataSource = dt
End Sub
Private Sub DistinctGroupButton_Click(sender As Object, e As EventArgs) Handles DistinctGroupButton.Click
Dim query = DataGridView1.Rows.OfType(Of DataGridViewRow)().
Where(Function(row) Not row.IsNewRow).
GroupBy(Function(row)
Return New With {Key .Priority = row.Cells("Priority").Value,
Key .Status = row.Cells("Status").Value}
End Function).
Select(Function(group) New With
{
Key .Value = group.Key,
Key .Count = group.Count(),
Key .Rows = group.ToList()
}
).
OrderByDescending(Function(x) x.Count).
ToList
Dim sb As New StringBuilder
For Each item In query
If item.Count > 1 Then
sb.AppendLine($"{item.Value.Priority},{item.Value.Status} count {item.Count}")
For Each row In item.Rows
sb.AppendLine($" row: {row.Index}")
Next
End If
Next
TextBox1.Text = sb.ToString()
End Sub
End Class
@karenpayneoregon
Copy link
Author

I'm going to be busy for a while so I suggest taking time with setting a breakpoint and inspecting what is being returned to learn than adapt as needed. Of course you can also asking in Stackoverflow or Microsoft Q&A forums.

@shemavalentin
Copy link

Thank you so much! I appreciate your help

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment