-
Jul 1st, 2017, 04:33 AM
#1
Thread Starter
Junior Member
The largest and the smallest numbers in a DataGridView
=LARGE(A1:A120;1)
=LARGE(A1:A120;2)
=SMALL(A1:A120;1)
=SMALL(A1:A120;2)
.
.
.
These are Excel functions to find the the largest and the smallest numbers in a column.
Like in excel, I want to find these numbers in a Visual Basic project. I have a DataGridView (120 Rows) and I want to find the first ten (1., 2.,3.,4.,5.,6.,7.,8.,9.,10.) largest and the smallest numbers. I will appreciate any idea to solve this problem. Thanks a lot.
-
Jul 1st, 2017, 04:59 AM
#2
Re: The largest and the smallest numbers in a DataGridView
Hello,
this will find the numbers < 10
where does the Data come from ? a Database ?
Code:
Private Sub ApplyFormatting()
For Each row As DataGridViewRow In DataGridView1.Rows
'number in Cell(2) < 10
Select Case CInt(row.Cells(2).Value)
Case Is < 10
row.Cells(2).Style.BackColor = Color.Aquamarine
'number > 100
Case Is > 100
row.Cells(2).Style.BackColor = Color.Pink
End Select
Next
End Sub
regards
Chris
-
Jul 1st, 2017, 06:50 AM
#3
Re: The largest and the smallest numbers in a DataGridView
curiousman
I'm a VB6 kinda guy, so I'm not familiar with DataGridView syntax.
However, one approach might be to dump the data from the DGV into an array and then sort it.
Here is a VB6 code frag to do a bubble sort.
It starts with a text string, so you'll need to modify things a bit.
Code:
txt0 = "1,11,6,3,8,5,9,10" ' orignal string
txt1 = "" ' sorted string
'
xtr0 = Split(txt0, ",") ' "temp" array for split (values are strings)
nn = UBound(xtr0)
' 0. populate "real" array with values
Dim aXTR()
ReDim aXTR(nn)
For ii = 0 To nn
aXTR(ii) = Val(xtr0(ii))
Next ii
ns = 0
' 1. do bubble sort
For ii = nn To 0 Step -1
didswap = 0
For jj = 0 To ii - 1
If aXTR(jj) > aXTR(jj + 1) Then
swap = aXTR(jj)
aXTR(jj) = aXTR(jj + 1)
aXTR(jj + 1) = swap
didswap = 1
ns = ns + 1
End If
Next jj
If didswap = 0 Then
Exit For
End If
Next ii
' 2. create "sorted" txt string
For ii = 0 To nn
txt1 = txt1 + Trim(aXTR(ii)) + IIf(ii = nn, "", ",")
Next ii
Once you've sorted the 120 rows of col 1, you could "pick" the 1st 10 (smallest) and last 10 (largest).
Then, you could repeat the process for col 2.
You can omit step 2 .. I included it for demonstration purposes.
HTH.
Spoo
Last edited by Spooman; Jul 1st, 2017 at 06:54 AM.
-
Jul 1st, 2017, 06:51 AM
#4
Thread Starter
Junior Member
Re: The largest and the smallest numbers in a DataGridView
I think I asked the question imperfect.
I have a DGV. It's name is HesapsonDataGridView.
In 32. Column I have numbers 120 cells. And I want to write the first ten largest number of this Column32 in Column33. And the first the smallest numbers of this Column32 in Column34. Like this picture.
Thank you very much for your answer. If you solve the problem like this I will be grateful.
-
Jul 1st, 2017, 06:59 AM
#5
Re: The largest and the smallest numbers in a DataGridView
Originally Posted by curiousman
Thank you very much for your answer. If you solve the problem like this I will be grateful.
We seem to be overlapping our replies.
Are you responding to my post #3, or to ChrisE's post #2?
Spoo
-
Jul 1st, 2017, 08:18 AM
#6
Thread Starter
Junior Member
Re: The largest and the smallest numbers in a DataGridView
I responded ChrisE, because I saw your answer now.
Your approach would be fine if I had only one data. My friend, I have a textbox and I am writing a name in it. So according to the name project brings 120 cell data from a database. Shortly the data changes according to the name. So your approach is not practical for me.
Thanks a lot
P.S.= If I were you, I started VB.NET. I understood that you have a good VB6 knowledge. You can do many good projects after devoloping the VB knowledge in a short time.
-
Jul 1st, 2017, 09:12 AM
#7
Re: The largest and the smallest numbers in a DataGridView
Originally Posted by curiousman
I have a textbox and I am writing a name in it. So according to the name project brings 120 cell data from a database. Shortly the data changes according to the name. So your approach is not practical for me.
Sorry you feel that way, but ...
- Issue regarding the textbox was not mentioned before.
- Nonetheless, once you get the 120 cell data from the database, this would be the time to try the bubble sort.
- You also mention that shortly the data changes according to the name.
- what does that mean?
- does it mean you get a different set of data that you also need to sort?
- if so, then you could do another bubble sort.
- After each bubble sort, you could then easily post the results into your DGV.
- Unless DGV has a sort capability, seems to me that you'll need to do a sort of some kind in order to identify the top 10 and bottom 10.
Spoo
Last edited by Spooman; Jul 1st, 2017 at 09:22 AM.
-
Jul 1st, 2017, 01:33 PM
#8
Re: The largest and the smallest numbers in a DataGridView
If this data is coming from a database like Sql Server or MS Access then you can handle this issue with your SQL statement
Here is an Access example,
Smallest
Code:
Select Top 10 column32, theNameColumn From lots where theNameColumn='someName' ORDER BY column32 ASC
Largest
Code:
Select Top 10 column32, theNameColumn From lots where theNameColumn='someName' ORDER BY column32 DESC
I think SQL Server uses TOP(10)
Last edited by wes4dbt; Jul 1st, 2017 at 04:24 PM.
-
Jul 1st, 2017, 02:46 PM
#9
Re: The largest and the smallest numbers in a DataGridView
wes
Of course .. ORDER BY
Nice ..
Spoo
Tags for this Thread
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
|