[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Sorting into the 2 dimensional Array

Posted on 1999-11-11
20
Medium Priority
?
254 Views
Last Modified: 2013-11-13
To,
experts
I have no. of textboxes loaded on the form using control array.What i want to do is when i insert integer values into that textboxes randomaly then after if i press "ok" button then it gives me all that values in sorted form(ascending order).

I mean in 2 dimensional array what i want is like "array(index,textval)".

Example:
So when my form is loaded then i insert value in this way.

Name of Textbox ---->      Having values
Text(1)        -- >           5
Text(2)        -- >           3
Text(3)        -- >           2
Text(4)        -- >           4
Text(5)        -- >           1

What I want into the array is :I mean text value wise sorting.

In first column it shows me corresponding "index" of that values which is available in second column.
      Index            Sorted Values
      5      -->            1
      3      -->            2
2      -->            3
      4      -->            4
      1      -->            5

Thanks,
bhavesh
0
Comment
Question by:bhavesh55
  • 8
  • 8
  • 4
20 Comments
 

Author Comment

by:bhavesh55
ID: 2200944
Edited text of question.
0
 

Author Comment

by:bhavesh55
ID: 2200946
Edited text of question.
0
 

Author Comment

by:bhavesh55
ID: 2200947
Edited text of question.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 14

Accepted Solution

by:
mcrider earned 40 total points
ID: 2200983
You could use a hidden listbox with the sort property set to true...

basically, you do a loop like this:

   for i = 0 to text1.ubound
      list1.AddItem format(val(Text1(i).text),"0000000000")
      list1.ItemData(list1.NewIndex)=i
   next i


Then when you walk through the listbox, the associated ItemData property will be the number of the textbox...

   for i = 0 to list1.listcount-1
      debug.print cstr(list1.itemdata(i)+1)+"-->"+list1.list(i)
   next i
0
 
LVL 14

Expert Comment

by:mcrider
ID: 2200988
You can use this debug.print line to get the values without leading zeroes:

   For i = 0 To List1.ListCount - 1
      Debug.Print CStr(List1.ItemData(i) + 1) + "-->" + CStr(Val(List1.List(i)))
   Next i


Cheers!
0
 

Author Comment

by:bhavesh55
ID: 2201489
To,
mcrider
I don't want to use Listbox.So is there any facility of directly sort into the array.

Or any other way without using listbox.Then let me know.
Thanks,
bhavesh
0
 
LVL 14

Expert Comment

by:mcrider
ID: 2201509
Check out this microsot KB article...

FILE: How to Sort Algorithms for Numeric Arrays
http://support.microsoft.com/support/kb/articles/Q169/6/17.asp 

Cheers!
0
 

Author Comment

by:bhavesh55
ID: 2209951
To,
mcrider
That's fine for sorting.But actually i want to sort in 2 dimensional array.

I mean in first column i want to assign index values for those textboxes which having values.And in second column i want to assign values of corresponding  textboxes.

And i want to sort according to second column.

Please send me your reply as soon as possible.

Thanks,
bhavesh
0
 
LVL 14

Expert Comment

by:mcrider
ID: 2209971
You can put the two arrays together seperated by a character into a third array and sort it, then break out the array.

Cheers!
0
 

Expert Comment

by:buddyp
ID: 2210997
To,
mcrider
I think you couldn't get my point.I already explain you on very first stage when i ask question.So please refer that one and send me your reply as soon as possible.

Please send me detail code for that.

Thanks,
buddy
0
 

Expert Comment

by:buddyp
ID: 2210999
I mean i want to sort on second column and also corresponding values is to be able to retrieve on first column too.

I am eagerly waiting for your reply.

Send me your code.

Thanks,
buddy
0
 
LVL 14

Expert Comment

by:mcrider
ID: 2211714
This is what I was talking about...

Add the following code to a MODULE:

'-------------------------------------------------------------------------------
Function GetField(Source As String, Delimiter As String, Optional Position As Variant) As String
    'THIS FUNCTION RETURNS THE SPECIFIED FIELD IN A STRING
    Dim iVal As Integer
    Dim jVal As Integer
    Dim kVal As Integer
    Dim tBuf As String
    jVal = 0: kVal = 1
    tBuf = Source + Delimiter
    If IsMissing(Position) = False Then
        For iVal = 1 To Val(Position) - 1
            jVal = InStr(jVal + 1, tBuf, Delimiter)
            If jVal = 0 Then
                GetField = ""
                Exit Function
            End If
        Next iVal
        kVal = jVal + 1
    End If
    iVal = InStr(jVal + 1, tBuf, Delimiter)
    If iVal = 0 Then
        GetField = ""
    Else
        GetField = Mid$(tBuf, kVal, iVal - kVal)
    End If
End Function

Sub SortArray(InArray As Variant, FieldNumber As Long, Delimit As String)
    'THIS FUNCTION SORTS AN ARRAY BASED ON THE FIELDNUMBER IN THE ARRAY
    Dim lLoop1 As Long
    Dim lLoop2 As Long
    Dim tBuf As String
    For lLoop1 = UBound(InArray) To LBound(InArray) Step -1
        For lLoop2 = LBound(InArray) + 1 To lLoop1
            If GetField(CStr(InArray(lLoop2 - 1)), Delimit, FieldNumber) > _
                GetField(CStr(InArray(lLoop2)), Delimit, FieldNumber) Then
                tBuf = InArray(lLoop2 - 1)
                InArray(lLoop2 - 1) = InArray(lLoop2)
                InArray(lLoop2) = tBuf
            End If
        Next lLoop2
    Next lLoop1
End Sub
'-------------------------------------------------------------------------------



Then you can build and sort your array this way:

'-------------------------------------------------------------------------------
    Dim X As Variant
    Dim iVal As Long
   
    X = Array("1:Mike", "2:Allen", "3:Jean", "4:Jay")
    For iVal = 0 To UBound(X)
        Debug.Print X(iVal)
    Next iVal
    SortArray X, 2, ":"
    Debug.Print "----------------------"
    For iVal = 0 To UBound(X)
        Debug.Print X(iVal)
    Next iVal
'-------------------------------------------------------------------------------

In the above code, the array X has been hardcoded with the array... In the first "record" of the array "1:Mike"

     "1" is the textbox number
     "Mike" is the textbox contents
     ":" is the delimiter used to separate the fields.

When the SortArray function is called, it takes the X Array and sorts the second field using the delimiter ":" to define the field.


By the way, this answer is really worth more than 25 points. ;-)


Cheers!

0
 

Author Comment

by:bhavesh55
ID: 2222390
To,
mcrider
How to run your code ?I mean which part of your code should i have to put under module and which part
under form ?

Where should i have to pass parameters and in which form ?please explain me in detail.

I am eagerly waiting for your reply.

Thanks,
buddy
0
 
LVL 14

Expert Comment

by:mcrider
ID: 2222393
Everything between the first set of lines goes in a module.

Everything between the second set of lines is an example of how to use the code....

Cheers!
0
 

Author Comment

by:bhavesh55
ID: 2222697
To,
mcrider

You are very close to my requirement but you did something different.

When i ask question at that time i show you that i want to search in second column of my array.You did it for "Char" value, actually i ask you to do it for "numerical value".I mean you are very close but i want something different.

e.g.

1 - 9
2 - 4
3 - 1
4 - 6

When i sort in array having above mention values.Then i expecting output as :

3 - 1
2 - 4
4 - 6
1 - 9

Please send me your reply as soon as possible.I don't want anything more except i mention above.

Thanks for your time but help me last time.
Thanks,
bhavesh
0
 

Author Comment

by:bhavesh55
ID: 2223436
To,
mcrider
Please help me last time which i mention above.

If you want more points then i will give you.But reply me last time.

I appreciate your help.

I am eagerly waiting for your reply.

Thanks,
bhavesh
0
 
LVL 14

Expert Comment

by:mcrider
ID: 2223681
Add the following code to the module I already gave you. You can then call SortArrayNumeric instead of SortArray.  This will sort the column based on it's numeric value instead of it's character value.


'-------------------------------------------------------------------------------
Sub SortArrayNumeric(InArray As Variant, FieldNumber As Long, Delimit As String)
    'THIS FUNCTION SORTS AN ARRAY BASED ON THE FIELDNUMBER IN THE ARRAY
    Dim lLoop1 As Long
    Dim lLoop2 As Long
    Dim tBuf As String
    For lLoop1 = UBound(InArray) To LBound(InArray) Step -1
        For lLoop2 = LBound(InArray) + 1 To lLoop1
            If Val(GetField(CStr(InArray(lLoop2 - 1)), Delimit, FieldNumber)) > _
                Val(GetField(CStr(InArray(lLoop2)), Delimit, FieldNumber)) Then
                tBuf = InArray(lLoop2 - 1)
                InArray(lLoop2 - 1) = InArray(lLoop2)
                InArray(lLoop2) = tBuf
            End If
        Next lLoop2
    Next lLoop1
End Sub
'-------------------------------------------------------------------------------


Cheers!
0
 

Expert Comment

by:buddyp
ID: 2229296
To,
mcrider
Suppose i inserted the values of Textbox's index and Textbox's values
into 2 dimensional array accordingly.

I don't know what value are there in it.I have to pass that values into
your code.But i get confused when i reached at below mentioned line of
your code.

    X = Array("1:Mike", "2:Allen", "3:Jean", "4:Jay")

How to dynamically pass this values ?

I am eagerly waiting for your reply.

Please...
Thanks,
buddy
0
 

Expert Comment

by:buddyp
ID: 2229304
To,
mcrider

Pleae help me last time.

Thanks for your time.

Thanks,
buddy
0
 
LVL 14

Expert Comment

by:mcrider
ID: 2229534
The line:

   X = Array("1:Mike", "2:Allen", "3:Jean", "4:Jay")

is just a hard-coded array... By the way you worded your question, I thought you knew how to build the array... Sorry.  Here's a step-by-step example of how to use the code:

1) create a new project.

2) add a new module to your project.

3) in the module, paste the following code:

'-----------------------------------------------------------
    Sub SortArrayNumeric(InArray As Variant, FieldNumber As Long, Delimit As String)
        'THIS FUNCTION SORTS AN ARRAY BASED ON THE FIELDNUMBER IN THE ARRAY
        Dim lLoop1 As Long
        Dim lLoop2 As Long
        Dim tBuf As String
        For lLoop1 = UBound(InArray) To LBound(InArray) Step -1
            For lLoop2 = LBound(InArray) + 1 To lLoop1
                If Val(GetField(CStr(InArray(lLoop2 - 1)), Delimit, FieldNumber)) > _
                    Val(GetField(CStr(InArray(lLoop2)), Delimit, FieldNumber)) Then
                    tBuf = InArray(lLoop2 - 1)
                    InArray(lLoop2 - 1) = InArray(lLoop2)
                    InArray(lLoop2) = tBuf
                End If
            Next lLoop2
        Next lLoop1
    End Sub
    Function GetField(Source As String, Delimiter As String, Optional Position As Variant) As String
        'THIS FUNCTION RETURNS THE SPECIFIED FIELD IN A STRING
        Dim iVal As Integer
        Dim jVal As Integer
        Dim kVal As Integer
        Dim tBuf As String
        jVal = 0: kVal = 1
        tBuf = Source + Delimiter
        If IsMissing(Position) = False Then
            For iVal = 1 To Val(Position) - 1
                jVal = InStr(jVal + 1, tBuf, Delimiter)
                If jVal = 0 Then
                    GetField = ""
                    Exit Function
                End If
            Next iVal
            kVal = jVal + 1
        End If
        iVal = InStr(jVal + 1, tBuf, Delimiter)
        If iVal = 0 Then
            GetField = ""
        Else
            GetField = Mid$(tBuf, kVal, iVal - kVal)
        End If
    End Function
    Sub SortArray(InArray As Variant, FieldNumber As Long, Delimit As String)
        'THIS FUNCTION SORTS AN ARRAY BASED ON THE FIELDNUMBER IN THE ARRAY
        Dim lLoop1 As Long
        Dim lLoop2 As Long
        Dim tBuf As String
        For lLoop1 = UBound(InArray) To LBound(InArray) Step -1
            For lLoop2 = LBound(InArray) + 1 To lLoop1
                If GetField(CStr(InArray(lLoop2 - 1)), Delimit, FieldNumber) > _
                    GetField(CStr(InArray(lLoop2)), Delimit, FieldNumber) Then
                    tBuf = InArray(lLoop2 - 1)
                    InArray(lLoop2 - 1) = InArray(lLoop2)
                    InArray(lLoop2) = tBuf
                End If
            Next lLoop2
        Next lLoop1
    End Sub
'-----------------------------------------------------------


4) on Form1, add a textbox to the form, then copy that textbox and paste it on your form 3 more times.  Make sure you create a control array (You will have Text1 with index 0-3

5) add a command button to the form.

6) add the following code to the click event of the command button:

'-----------------------------------------------------------
    Dim xArray() As String
    Dim iVal As Long
   
    ReDim xArray(Text1.UBound)
    For iVal = 0 To Text1.UBound
        xArray(iVal) = CStr(iVal + 1) + ":" + Text1(iVal).Text
    Next iVal
   
    SortArrayNumeric xArray, 2, ":"
   
    For iVal = 0 To Text1.UBound
        Debug.Print xArray(iVal)
    Next iVal
'-----------------------------------------------------------

7) run the program.  in the textboxes enter 300, 35, 40 and 100.

8) click the command button and watch the immediate window...


Cheers!  


0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question