MSFlexgrid countingsort

AWaterfalls
AWaterfalls used Ask the Experts™
on
Dear Experts, I have a MSFlexGrid1 With 5 colums of integer data, max integer is 1 to 50.
I would like to countsort these colums, so that I end up with a list in a second MSFlexgrid2, of the counted and sorted data like the list below. In the second MSFlexgrid2 there should be only two colums as shown below. The code will need to identify the individual numbers from 1 to 50, and show how many times they are repeated in the 5 columns. I am using vb6. Your help would be appreciated, thank you.

Number    Frequency
1              80
2              65
3              45
4              90
etc           etc
50            120
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
Try this:

Private Sub Command1_Click()

  On Error GoTo Err_Handler

  Dim c As Long
  Dim r As Long
  
  Dim nValue As Long
  Dim nCountColumns As Long
  Dim aNumers(1 To 50) As Long
  
  c = 0
  nCountColumns = 5
  
  With MSFlexGrid1
      For c = 0 To nCountColumns - 1
    
          ' Sort by Column
          .Col = c
          .ColSel = 0
          .Sort = flexSortGenericAscending
      
          nValue = 0
          
          For r = 1 To .Rows - 1
              If Len(.TextMatrix(r, .Col)) > 0 Then
                  If IsNumeric(.TextMatrix(r, .Col)) And ((.TextMatrix(r, .Col) > 0) And (.TextMatrix(r, .Col) <= 50)) Then
                      If r = 1 Then
                          nValue = Val(.TextMatrix(r, .Col))
                          
                          aNumers(nValue) = aNumers(nValue) + 1
                      Else
                          If CheckRow(r, .Col, nValue) = False Then
                              nValue = Val(.TextMatrix(r, .Col))
                          End If
                              
                          aNumers(nValue) = aNumers(nValue) + 1
                      End If
                  End If
              End If
          Next r
      Next c

  End With
  
  Call FillCountGrid(aNumers(), MSFlexGrid2)

  Exit Sub

Err_Handler:
  Debug.Print "ERROR (Command1_Click): " & CStr(Err.Number) & ", " & Err.Description

End Sub

Private Function CheckRow( _
                    ByVal nRow As Long, _
                    ByVal nCol As Long, _
                    ByVal nValue As Long _
                    ) As Boolean
  
  With MSFlexGrid1
      If nRow <= .Rows - 1 Then
          If IsNumeric(.TextMatrix(nRow, nCol)) And ((.TextMatrix(nRow, nCol) > 0) And (.TextMatrix(nRow, nCol) <= 50)) Then
              If Val(.TextMatrix(nRow, nCol) = nValue) Then
                  CheckRow = True
              End If
          End If
      End If
  End With
  
  Exit Function

End Function

Private Sub FillCountGrid( _
                ByRef aNumers() As Long, _
                ByRef oFlexGrid As MSFlexGrid _
                )
                
  On Error GoTo Err_Handler

  Dim Entry As Variant
  Dim i As Long
  Dim j As Long
                                 
  With oFlexGrid
      .Cols = 2
      .FixedRows = 1
      .Rows = .FixedRows
      
      .TextMatrix(0, 0) = "Number"
      .TextMatrix(0, 1) = "Frequency"
      
      For i = 1 To 50
          Entry = i & Chr(9) & aNumers(i)
          
          .AddItem Entry
      Next i
      
  End With

  Exit Sub
  
Err_Handler:
  Debug.Print "ERROR (FillCountGrid): " & CStr(Err.Number) & ", " & Err.Description

End Sub

Open in new window

Top Expert 2012

Commented:
Version 2:

Private Sub Command1_Click()

  On Error GoTo Err_Handler

  Dim c As Long
  Dim r As Long
  
  Dim nValue As Long
  Dim nCountColumns As Long
  Dim aNumers(1 To 50) As Long
  
  nCountColumns = 5
  
  With MSFlexGrid1
      nValue = 0
      
      For r = 1 To .Rows - 1
          For c = 0 To nCountColumns - 1
              If Len(.TextMatrix(r, c)) > 0 Then
                  If IsNumeric(.TextMatrix(r, c)) Then
                      nValue = Val(.TextMatrix(r, c))
                      If (nValue > 0) And (nValue <= 50) Then
                          aNumers(nValue) = aNumers(nValue) + 1
                      End If
                  End If
              End If
          Next c
      Next r
  End With
  
  Call FillCountGrid(aNumers(), MSFlexGrid2)
  
  Exit Sub

Err_Handler:
  Debug.Print "ERROR (Command1_Click): " & CStr(Err.Number) & ", " & Err.Description

End Sub

Open in new window

Author

Commented:
Version 2, Worked very well. Thank you, you get maximum points for that answer.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for AWaterfalls's comment #a38731535

for the following reason:

It Just Worked First Tme.
Top Expert 2012

Commented:
Hi AWaterfalls,
Thank you,
you should accept my answer as solution.
Please read here how to close the question:
http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=24
Top Expert 2012

Commented:
Accept: eemit {http:#a38729340}

In her post {http:#a38731535}
Asker said:
"Version 2, Worked very well. Thank you, you get maximum points for that answer."

In her post {http:#a38731585}
Asker accidentally accept his own Answer:
(Version 2, Worked very well. Thank you, you get maximum points for that answer.)
Asker said as Grading Comment:
"It Just Worked First Tme."

eemit

Author

Commented:
I am sorry but I am New to all this, however the first solution gave only zeros for the frequency on the second flexgrid. While solution 2 gave the frequencies of the balls as per requested by me. Thank you for your help, and I look forward to using experts again in the future.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial