Results 1 to 3 of 3

Thread: Datatable expression COUNTIF or equivalent

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2013
    Posts
    61

    Datatable expression COUNTIF or equivalent

    Hi there..

    In Excel it's possible to do a COUNTIF function on a column... eg say I had a named range called REF filled with

    A
    A
    A
    A
    B
    A
    B

    formula would be =COUNTIF(REF,"A")

    In my datatable I have a load of numbers in a column called REF

    111
    222
    222
    333
    333
    333

    I want to be able to calculate in the end column on each ROW the count of column REF if the column = what's currently in REF

    so the count would show

    1
    2
    2
    3
    3
    3

    Can this be done using basic expressions?

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Datatable expression COUNTIF or equivalent

    here's how to count datatable rows conditionally:

    Code:
    Public Class Form1
    
        Dim dt As New DataTable
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            dt.Columns.Add("REF", GetType(Integer))
            dt.Rows.Add(111)
            dt.Rows.Add(222)
            dt.Rows.Add(222)
            dt.Rows.Add(333)
            dt.Rows.Add(333)
            dt.Rows.Add(333)
            ComboBox1.DisplayMember = "REF"
            ComboBox1.DataSource = dt
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
            Label1.Text = dt.Rows.Cast(Of DataRow).Count(Function(dr) CInt(dr.Item("REF").ToString) = CInt(ComboBox1.Text)).ToString
        End Sub
    
    End Class
    this shows you the method for counting. you'd just need to loop through the rows for what you want...

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Datatable expression COUNTIF or equivalent

    One thing to keep in mind is that every means of doing this is going to have to visit all the rows, and possibly do so multiple times. There's no getting around that, because a datatable is just a datatable. There's no indexed list of the contents. Therefore, what .Paul. showed may be the shortest way to write such a thing, but if you find it hard to read, the longhand approach will work just as well (though it will take up MANY more lines than just one).

    Therefore, you might also consider the context in which you are doing this. For example, if this is a one time thing, then just performing the count one time is the only way to go. If you will be adding/removing lines, and wanting to keep the field up to date as the rows change, then building your own index would make more sense. An index would probably be a Dictionary(of string,integer), where the key (the string) is the value you are looking for (those look like integers, but I'm guessing that they are just an example, and that the actual value might be a string), and the value is the count. If you built such an index, then every time you added a row, you could update your index, and every time you deleted a row, you could reduce your index.

    In fact, you could probably come up with an even more clever index based on something like a Dictionary(of string, List(of Datarow)), but that could get exotic.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width