none
Count of unique values from one of the column of datagrid RRS feed

  • Question

  • Hi Pals,

    I want to find the count of unique values from a datagridview, is this possible in VB.Net ?

    See below example;


    Now I want to get the unique values from Priority/Status column with their names and count.

    Any help would be greatly appreciated.

    Thanks,

    Srini


    Tuesday, November 19, 2013 2:42 PM

Answers

All replies

  • Try this..objTable is your datatable

      Dim uniqueRows = objTable.AsEnumerable().GroupBy(Function(row) New With { _
            Key .Priority = DirectCast(row("Priority"), String), _
            Key .Status = DirectCast(row("Status"), String) _       
            })
    
            Dim result = From el In uniqueRows
                          Select String.Format("Priority:{0} Status:{1} Count:{2}", el.Key.Priority, el.Key.Status, el.Count())
            MessageBox.Show(String.Join(Environment.NewLine, result))

    Tuesday, November 19, 2013 3:38 PM
  • Hello,

    Just a slight tweak to Venkat786 solution, no need to cast fields as shown below

    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(Int32),
                    .AutoIncrement = True,
                    .AutoIncrementSeed = 1
                }
            )
            dt.Columns.Add(
                New DataColumn With
                {
                    .ColumnName = "Priorty",
                    .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"})
    
            Dim uniqueRows = dt.AsEnumerable() _
                             .GroupBy(Function(row) _
                             New With
                             {
                                 Key .Priority = row.Field(Of String)("Priorty"),
                                 Key .Status = row.Field(Of String)("Status")
                            }
                        )
    
    
            Dim result = From el In uniqueRows
                          Select String.Format("Priority:{0} Status:{1} Count:{2}", el.Key.Priority, el.Key.Status, el.Count())
            MessageBox.Show(String.Join(Environment.NewLine, result))
    
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, November 19, 2013 4:02 PM
    Moderator
  • Hi

    Here is one way, using a DataGridView alone, without any DataTable or Bindings/ Not as elegant as the other solutions offered here, but still it works. It works for any new terms entered into the DataGridView.  This code relies on the Form1 having a DataGridView with 5 columns.  The extra 2 columns are for the Combined strings and for the Count values. Either of which could be hidden columns. The code uses the column names, so, for this example the DataGridView needs these columns: "Column1","Priority","Status","Comb","Count".  This code fills the DataGridView with random data for testing.

    Form1 Load event (just for dummy data, and/or columns could be hidden here too)

    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load Dim p() As String = {"Low", "Medium", "High"} Dim n() As String = {"New", "Old"} Dim r As New Random For i As Integer = 1 To 20 Dim pr As String = p(r.Next(0, 3)) Dim nr As String = n(r.Next(0, 2)) Me.DGV.Rows.Add(i, pr, nr, pr & "|" & nr) Next

    CountThem() End Sub


    Sub for DataGridView Row Validating event

      Private Sub DGV_RowValidating(sender As Object, e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DGV.RowValidating
            Select Case e.ColumnIndex
                Case 1, 2
                    If Not IsNothing(DGV("Priority", e.RowIndex).Value) AndAlso Not IsNothing(DGV("Status", e.RowIndex).Value) Then
                        DGV("Comb", e.RowIndex).Value = DGV("Priority", e.RowIndex).Value.ToString & "|" & DGV("Status", e.RowIndex).Value.ToString
                    End If
            End Select
            CountThem()
        End Sub

    Sub to find the Unique values and Counts

      Private Sub CountThem()
            Dim dic As New Dictionary(Of String, Integer)
            For Each r As DataGridViewRow In DGV.Rows
                Try
                    dic.Add(r.Cells(3).Value.ToString, 1)
                Catch ex As Exception
                    If ex.Message = "An item with the same key has already been added." Then
                        dic(r.Cells(3).Value.ToString) += 1
                    End If
                End Try
            Next
            For Each r As DataGridViewRow In DGV.Rows
                If  Not IsNothing(r.Cells("Comb").Value) AndAlso Not r.Index = DGV.NewRowIndex Then
                    r.Cells("Count").Value = dic(r.Cells("Comb").Value.ToString).ToString
                End If
            Next
        End Sub


    Regards Les, Livingston, Scotland


    • Edited by leshay Tuesday, November 19, 2013 4:32 PM
    Tuesday, November 19, 2013 4:29 PM
  • I tried as you suggested but I am getting an error.

    Please see the screen shots below;


    Wednesday, November 20, 2013 10:33 AM
  • Error Screen shot

    Wednesday, November 20, 2013 10:34 AM
  • My requirement is to extract the unique values from a datagrid and draw a char from that data.
    Wednesday, November 20, 2013 10:35 AM
  • I got the simple solution to find the unique elements from below sample code;

    Dim names = From row As DataGridViewRow In DataGridView1.Rows
                Select (CStr(row.Cells(1).Value)) Distinct

            For Each n In names
                MessageBox.Show(n)
            Next

    But how can I get the count of these unique elements ?

    Please suggest.


    Wednesday, November 20, 2013 11:15 AM
  • Srinivas : Try replacing the first two lines in button click event with below and see how it goes.

    	 
            Dim dv As DataView = DirectCast(DataGridView1.DataSource, DataView)
            Dim objTable = dv.ToTable()

    Wednesday, November 20, 2013 3:04 PM
  • Srinivas : Try replacing the first two lines in button click event with below and see how it goes.

    	 
            Dim dv As DataView = DirectCast(DataGridView1.DataSource, DataView)
            Dim objTable = dv.ToTable()

    Thanks Venkat but now I got below error.

    Wednesday, November 20, 2013 3:35 PM
  • You might want to try this

    https://skydrive.live.com/redir?resid=A3D5A9A9A28080D1!444&authkey=!ABS6bNQIo0GA8QM


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by Venkat786 Wednesday, November 20, 2013 9:10 PM
    • Marked as answer by Srinivas Merugu Thursday, November 21, 2013 8:16 AM
    Wednesday, November 20, 2013 4:44 PM
    Moderator
  • Excellent.... This is what I was asking for....

    Thanks you 

    Kareninstructor


    Thursday, November 21, 2013 8:17 AM
  • how if i use listview in vb.net.

    thanks before.

    Friday, September 8, 2017 2:12 PM
  • hi,

    Create another Datagridview same as the one one you displayed above without entries but with 1 extra cloumn for count and modify the the below code to count unique values

    I think that this will be easy to understand.

    Dim FoundGL As Boolean
            dgvGLSummation.Rows.Clear()
            For Each oRow In dgvItemsList.Rows
                If dgvGLSummation.Rows.Count = 0 Then
                    dgvGLSummation.Rows.Add(oRow)
                Else
                    For Each CurRow In dgvGLSummation.Rows
                        FoundGL = False
                        If CurRow.Cells(19).Value = oRow.Cells(19).Value Then
                            CurRow.Cells(13).Value = CurRow.Cells(13).Value + oRow.Cells(13).Value
                            FoundGL = True
                        End If
                        If FoundGL = False Then
                            dgvGLSummation.Rows.Add(oRow)
                        End If
                    Next
                End If
            Next

    Friday, November 17, 2017 12:59 PM
  • This is perfect.. but how do you do if you want to see 2 columns??
    Wednesday, November 7, 2018 8:33 PM
  • I know this is old...but Karen, do you still have the code?

    would like to see it. Thanks, B

    Wednesday, July 1, 2020 9:30 PM
  • I know this is old...but Karen, do you still have the code?

    would like to see it. Thanks, B

    Anything prior before 2015 I don't have but here is something very close.

    Source code is here which I just created for you.

    https://gist.github.com/karenpayneoregon/fd7ecfaf6e66307fe9277070ccc9b6ec

    here is a screenshot


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 1, 2020 11:30 PM
    Moderator
  • Thanks for the suggestion Karen.

    I recently submitted a new question of my own. Would welcome your thoughts on it...

    Not sure how to send it your way. Thanks, B

    Friday, July 3, 2020 7:43 PM