vb.net - count all duplicates data in Datagridview for certain criteria and put it on a chart

user_14883150 0
Viewed 84

So basically what I'm trying to do is count the duplicate values in a certain "column8" avoiding the rows that are "NO" in column17. The table would be like this:

column8 column17
014 NO
014
013 NO
013
014
017

and my code looks like this:

For Count As Integer = 0 To DataGridView1.Rows.Count - 1
    If DataGridView1.Rows(Count).Cells("Column17").Value <> "NO" Then
        Chart1.Series(0).Points.AddXY(DataGridView1.Item(8, Count).Value, 
                                      DataGridView1.Item(8, Count).Value)
    End If
Next Count

It get the names, but the count doesn't made sense at all. The result that I need is the chart with the name of the column8, with how many have repeat not including the ones that have "NO" in column17. Something like this:

014 2
013 1
017 1

Any help is really appreciated. Thanks!

datagridview vb.net
1 Answers

You could use LINQ to do the following:

  1. Query the DataGridViewRows
  2. Where the Row is not null, is not a new row, and the respective Column17 value is not "NO"
  3. Group by the Column8 value
  4. Return the first group's Column8 value along with the count

This will provide you with the unique values and desired count. For example:

Dim chartValues = From row In DataGridView1.Rows.OfType(Of DataGridViewRow)
                  Where
                      row IsNot Nothing AndAlso
                      Not row.IsNewRow AndAlso
                      Not row.Cells().Item("Column17").Value.Equals("NO")
                  Group By row.Cells().Item("Column8").Value Into rowGroup = Group
                  Order By rowGroup.Count() Descending
                  Select New With {
                      .Value = rowGroup.First().Cells("Column8").Value.ToString(),
                      .Count = rowGroup.Count()
                  }

I am not familiar enough with the Chart control to provide you with a confident example, but I think you would add data in this fashion:

For Each chartValue In chartValues
    Chart1.Series(0).Points.AddXY(chartValue.Value, chartValue.Count)
Next

UPDATE

Per the OP's request, here is a non-LINQ version of the above code (sans-sort)

Dim chartValues = New Dictionary(Of String, Integer)()
For Each row In DataGridView1.Rows.OfType(Of DataGridViewRow)
    If (row Is Nothing OrElse row.IsNewRow OrElse row.Cells().Item("Column17").Value.Equals("NO")) Then
        Continue For
    End If

    Dim column8Value = row.Cells().Item("Column8").Value.ToString()
    If (chartValues.ContainsKey(column8Value)) Then
        chartValues(column8Value) += 1
    Else
        chartValues.Add(column8Value, 1)
    End If
Next
user_19200350
Related