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.

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

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

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.
Attachment 149061

Thank you very much for your answer. If you solve the problem like this I will be grateful.

Quote:

---

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

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.

Quote:

---

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 ...

1. Issue regarding the textbox was not mentioned before.
2. Nonetheless, once you get the 120 cell data from the database, this would be the time to try the bubble sort.
3. 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.
4. After each bubble sort, you could then easily post the results into your DGV.
5. 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

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)

wes

Of course .. ORDER BY
Nice .. :thumb:

Spoo