-
Jul 25th, 2013, 02:03 PM
#1
Thread Starter
Member
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?
-
Jul 25th, 2013, 05:41 PM
#2
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...
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jul 25th, 2013, 06:09 PM
#3
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|