Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Jul 2009
    Location
    Cullman AL.
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    I’d like some help in sorting a list of two digit numbers. (Using the sample as a ‘for instance)

    “For instance” list of numbers;
    42 12 03 21 14
    02 21 33 19 24
    31 16 41 07 39

    First; I’d like to sort the Column list numerically on the first two digits.
    Ie; 02 nn nn
    31 nn
    42 nn
    Second; I’d like to sort each cell in the list numerically with the lowest digits on the left.
    Ie; 03 12 14 21 42
    07 16 31 39 41



    I need the ‘how to’ instructions or the VB formulas for those two problems. Something I can copy and paste in the right place.
    My head seems to be too old to absorb the instructions in MS Excel. I need help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Assuming that your data start in cell A1, and that columns B etc. are empty, you can use the following macro:

    Code:
    Sub StrangeSort()
      Dim arr As Variant
      Dim i As Long
      Dim m As Long
      Dim r As Long
      Dim n As Long
      Dim strVal As String
    
      ' Count number of elements per cell
      m = (Len(Range("A1")) + 1) \ 3
      ' Prepare FieldInfo array
      ReDim arr(1 To m)
      For i = 1 To m
    	arr(i) = Array(i, xlTextFormat)
      Next i
      ' Split data
      Range("A1").CurrentRegion.TextToColumns DataType:=xlDelimited, _
    	Destination:=Range("A1"), Space:=True, FieldInfo:=arr
      ' Sort on column A
      Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, _
    	Header:=xlNo, Orientation:=xlTopToBottom
      ' Last row
      n = Cells(Rows.Count, 1).End(xlUp).Row
       For r = 1 To n
    	' Sort each row horizontally
    	Range(Cells(r, 1), Cells(r, m)).Sort Key1:=Cells(r, 1), _
    	  Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
    	' Concatenate the values per row again
    	strVal = Cells(r, 1)
    	For i = 2 To m
    	  strVal = strVal & " " & Cells(r, i)
    	Next i
    	Cells(r, 1) = strVal
      Next r
      ' Clear auxiliary cells
      Range(Cells(1, 2), Cells(n, m)).ClearContents
    End Sub

  3. #3
    New Lounger
    Join Date
    Jul 2009
    Location
    Cullman AL.
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, just looking at your answer tells me I was never going to come up with the answer!

    I dont know how to load or make use of a Macro?
    Will your macro do both functions and put the answers in two different cells for me.
    In other words; If I had the known list in A-nn(a long list in column A) would your Macro put the first sort in B-nn and the second sort in C-nn?

    I'd wind up with the known numbers in A, the first sort in B and the second sort in C?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To get a macro into your workbook:
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Select Insert | Module to create an empty code module.
    - Copy the code from my reply, and paste it into the module.
    You can then run it from Excel by selecting Tools | Macro | Macros... (or pressing Alt+F8), selecting the name of the macro and clicking Run.

    The macro performs both sorts - it first sorts on the first two digits, then sorts each row.
    Did you want to have the result of the two sort actions separately?

  5. #5
    New Lounger
    Join Date
    Jul 2009
    Location
    Cullman AL.
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    [quote name='HansV' post='785460' date='20-Jul-2009 12:58']Did you want to have the result of the two sort actions separately?[/quote]

    Yessir. I want the original list in A, the first numeric sort in B and the second sort in C. In any three cells but in that order.

    Thanks again. It's gonna take a day for me to try this and get back with the results.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code I posted will not do what you want. I have attached a sample workbook with modified code:
    [codebox]
    Sub StrangeSort()
    Dim arr As Variant
    Dim i As Long
    Dim m As Long
    Dim r As Long
    Dim n As Long
    Dim strVal As String

    ' Count number of rows
    n = Cells(Rows.Count, 1).End(xlUp).Row
    ' Copy to column B
    Range("A1:A" & n).Copy Range("B1")
    ' Sort
    Range("B1:B" & n).Sort Key1:=Range("B1"), Order1:=xlAscending, _
    Header:=xlNo, Orientation:=xlTopToBottom
    ' Copy to column C
    Range("A1:A" & n).Copy Range("C1")
    ' Count number of elements per cell
    m = (Len(Range("C1")) + 1) \ 3
    ' Prepare FieldInfo array
    ReDim arr(1 To m)
    For i = 1 To m
    arr(i) = Array(i, xlTextFormat)
    Next i
    ' Split data
    Range("C1:C" & n).TextToColumns DataType:=xlDelimited, _
    Destination:=Range("C1"), Space:=True, FieldInfo:=arr
    For r = 1 To n
    ' Sort each row horizontally
    Range(Cells(r, 3), Cells(r, m + 2)).Sort Key1:=Cells(r, 3), _
    Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
    ' Concatenate the values per row again
    strVal = Cells(r, 3)
    For i = 2 To m
    strVal = strVal & " " & Cells(r, i + 2)
    Next i
    Cells(r, 3) = strVal
    Next r
    ' Clear auxiliary cells
    Range(Cells(1, 4), Cells(n, m + 2)).Clear
    End Sub
    [/codebox]
    You can run the code in the workbook by clicking the button below the sample data.
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Jul 2009
    Location
    Cullman AL.
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I worked with that first one till I got this error.

    "Compile Error"
    Invalid outside procedure!

    I went back to the forum and saw your post with the correction.
    I tried that and got the same error.
    I went to the help, it led me to lower the security level, but the error is the same.

    Maybe I'm not finishing the Macro part correctly?
    It leaves me with a Macro, and I dont know how to exit that properly and get back to the Xls page.
    Could that be the problem?

    Here's what it said;

    Invalid outside procedure


    The statement must occur within a Sub or Function, or a property procedure (Property Get, Property Let, Property Set). This error has the following cause and solution:

    An executable statement, Static or ReDim, appears at module level.
    Static is unnecessary at module level, since all module-level variables are static. Use Dim instead of ReDim at module level. To create a dynamic array at module level, declare it with Dim using empty parentheses.

    Note At module level, you can use only comments and declarative statements, such as Const, Declare, Deftype, Dim, Option Base, Option Compare, Option Explicit, Option Private, Private, Public, and Type. The Sub, Function, and Property statements occur outside the body of their procedures, but within the procedure declaration.

    For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).


    Does that help you out? It sure didn't help me out very much. Like an explanation in Greek!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The macro that I posted starts with "Sub StrangeSort()" and ends with "End Sub". Do you have any text above "Sub StrangeSort()" or below "End Sub" in your code module? For example, perhaps you inadvertently ended up with two "End Sub" lines.
    If so, remove the superfluous text.

  9. #9
    New Lounger
    Join Date
    Jul 2009
    Location
    Cullman AL.
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    [quote name='HansV' post='786081' date='24-Jul-2009 09:28']The macro that I posted starts with "Sub StrangeSort()" and ends with "End Sub". Do you have any text above "Sub StrangeSort()" or below "End Sub" in your code module? For example, perhaps you inadvertently ended up with two "End Sub" lines.
    If so, remove the superfluous text.[/quote]


    You were right. I had spaces before and after the Macro.
    I think the key is how I exit the macro input.
    The screen shows the macro.
    How do I exit that, and get back to the Xls. list?
    So far I have been moving the cursor to a blank spot on the page and then hitting the X out.

    I get the same error and the same links and I still dont know any more about what I'm doing.
    I never realized just what a marvelous piece of workmanship this Excel is till I started trying to sort these numbers.
    I live a long way from any school or college. But if I were closer, I'd take a course in it just because I admire it.
    Thanks.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can press Alt+F11 to switch between Excel and the Visual Basic Editor.

    If you still have problems, could you post a copy of the workbook with sensitive data altered or removed?

  11. #11
    New Lounger
    Join Date
    Jul 2009
    Location
    Cullman AL.
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's what I have that wont work.
    [attachment=84850:Hans_post.xls]
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The error message that you see is because you have the word CODE above the line

    Sub StrangeSort()

    That is not allowed, you must remove the word CODE.

    There is another problem - computers, and computer programs, are inflexible. In the example that you provided in the first post in this topic, the numbers were separated by spaces. So the code that I wrote looks for a space as separator.

    In the workbook that you attached, the numbers are separated by hyphens. You haven't adjusted the code for that, so it won't do what you want. The code must take the change from space to hyphen into account.

    There's a minor other problem: when I wrote the code, I didn't expect there to be blank cells in between non-blank cells in column A. In the attached version I have deleted those cells.

    Take a look at the attachment.
    Attached Files Attached Files

  13. #13
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='thudpucker' post='786127' date='24-Jul-2009 16:00']Here's what I have that wont work.
    [attachment=84850:Hans_post.xls][/quote]

    This is not a solution to your current problem but just a general comment.

    Whenever I begin a new Macro I always start with the menu bar with Tools, Macro, Record New Macro. When the Dialog Box Opens I Change the name of the Macro to the name I want. Example name = BoldCells.
    Then I select OK. The dialog box closes and a floating toolbar opens in your Excel sheet. Just hit stop recording on the floating toolbar.

    Now using Alt 11 open VBA and you should see a Macro something like this

    Sub BoldCells ()
    '
    ' BoldCells Macro
    ' Macro recorded 7/24/2009 by Tom Duthie
    '

    '
    End Sub

    Just add the need VBA lines of instruction below the opening Header and the last line "End Sub"

    In other words, use the Record Maco built into Excel to help you set up the beginning of your Macros. It saves time and reduces set up errors.
    Also any line that starts with a single quote is called a comment line and is not processed by VBA. VBA will show all comments in a different color than the VBA code lines. My computer shows comment lines in Green.

    Good luck with you current project.

    Tom Duthie

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='thudpucker' post='786127' date='24-Jul-2009 16:00']Here's what I have that wont work.
    [attachment=84850:Hans_post.xls][/quote]

    It doesn't work for three reasons:
    1. Hans has already advised you to remove the text "CODE" from the top of the module.
    2. You have changed the requirement. Your original post had the numbers separated by spaces; your current file separates them with a dash.
    3. The data in column A must be contiguous.
    Regards
    Don

  15. #15
    New Lounger
    Join Date
    Jul 2009
    Location
    Cullman AL.
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Lordy I feel like a Mechanic trying to make it through Med. School.

    Thanks all.
    I'll work on it tonight and let you know.
    Thanks for the tutorial duthiet.

    I thought I removed the word Code, but didn't eh?

    I have so many different efforts stored on my desk top. I'll just delete all of them and concentrate on the one I posted.

    From now on, I'll know to make sure any list I use is put in as the Macro says it wants it.
    I dont know where in the macro to change the parameters, so I'll change the data.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •