Results 1 to 9 of 9

Thread: The largest and the smallest numbers in a DataGridView

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    19

    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.

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    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

  3. #3
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    19

    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.
    Name:  DGV1.jpeg
Views: 582
Size:  25.6 KB

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

  5. #5
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: The largest and the smallest numbers in a DataGridView

    Quote Originally Posted by curiousman View Post
    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

  6. #6

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    19

    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.

  7. #7
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: The largest and the smallest numbers in a DataGridView

    Quote Originally Posted by curiousman View Post
    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
    Last edited by Spooman; Jul 1st, 2017 at 09:22 AM.

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  9. #9
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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
  •  



Click Here to Expand Forum to Full Width