Created
July 1, 2020 23:27
-
-
Save karenpayneoregon/fd7ecfaf6e66307fe9277070ccc9b6ec to your computer and use it in GitHub Desktop.
Group by two DataColumns
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
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.
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
Thank you so much! Now there is no errors. I tried it apart and works.
In my case I import excel sheet which among the columns there are two columns containing same data. for instance you can find 0700 in column1, and in column2 but on different row. I tried to iterate and count how many times a number occurs in both columns of a sheet and put its repetition as shown below but I'm failing to do so. Can you help me to achieve as the following?
I tried this but count in only one column and doesn't display result in a dgv.
private void btn_CountRepetition_Click(object sender, EventArgs e)
{
var q = dataGridView1.Rows.OfType()
.GroupBy(x => x.Cells[3].Value.ToString())
.Select(g => new { Value = g.Key, Count = g.Count(), Rows = g.ToList() })
.OrderByDescending(x => x.Count);
foreach (var x in q)
{
DialogResult Result = MessageBox.Show("Tel Number: " + x.Value + " " + " Repetion: " + x.Count + " " + "Times", "--------------------COUNT RESULT---------------", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (Result == DialogResult.OK)
{