Sort Text and Numbers:

  • Re: Sort Text and Numbers:


    Well, it's the same problem as the precedent macros. Civic numbers belong to their respective street and those streets to their repective sectors. So sectors are sorted. Then among a specific sector (for each sector) streets belonging to this sector are sorted inside that sector, and the same things apply to civic numbers sorted inside their specific street. It's exactly the same principle we've done previously. In fact, the last macro you've written does exactly what I just described. The only problem is that the first column is not numbers to be sorted but names in letters. It's only a matter of changing the type of characters to be sorted in the first column in this last macro.

  • Re: Sort Text and Numbers:


    The standard Excel sort does what you want (for up to three columns at a time). To do it for the selection (with no headers) with a macro, you can replace all of the earlier code with
    [vba]Sub SortThis()
    With Selection
    .Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 2), order2:=xlAscending, _
    key3:=.Cells(1, 3), order3:=xlAscending, header:=xlNo, Orientation:=xlTopToBottom
    End With
    End Sub
    [/vba]

  • Re: Sort Text and Numbers:


    Derk,


    I am the guy who started the original thread. By the way that code works great for me and I use it all the time.


    New request - but simular.


    I search the forum but found no answer.


    Can you write some code for sorting customer's name by last name when the first and last are in the same cell. I import and manipulate a lot of data from various programs for various customers. I am getting tired of changing text to colums then sorting by last name and putting back together. Unfortunatly most all of the data has the first and last name in the same cell.


    Example.


    Bill Clinton | Washington D.C.
    Bill Gates | Remond,WA
    Joe Adams | Detroit, MI



    Result
    Joe Adams | Detroit, MI
    Bill Clinton | Washington D.C.
    Bill Gates | Remond,WA



    Tia,


    Ross

  • Re: Sort Text and Numbers:


    Yay! Thanks Derks ;) Simplier code. I didn't know Excel was able to do it. Well I am glad it actually does. Your code works perfectly!


    Oh and hi Ross! Your thread has done a long run into sorting macros since your last post hehe. For your own problem, the way I see it, you would need to find the lenght of the last name until you find an empty space. The problem is if you have a second last name without any space.
    (E.g. Delano Roosevelt Franklin = Roosevelt Franklin Delano). It's the way I see it. There might be an other way, but that's the one I see personnally.


    Regards,


    Werner

  • Re: Sort Text and Numbers:


    Hi Ross,
    Try the following modification for sorting last names. Note that Excel 2000 or later is required. The code will not sort correctly for people with Jr. and similar things after their name, but could be modified to account for that. as with the code above, it functions on the selection and assumes no header rows.
    [vba]Sub SortbyLast()
    Dim r As Range, c As Range
    Application.ScreenUpdating = False
    Set r = Selection
    r.Columns(2).Insert xlToRight
    For Each c In r.Columns(1).Cells
    c.Offset(0, 1) = Mid(c, InStrRev(c, " ") + 1) 'InStrRev only in Excel 2000 and later
    Next c
    r.Resize(, r.Columns.Count + 1).Sort key1:=r.Cells(1, 2), order1:=xlAscending, _
    key2:=r.Cells(1, 1), order2:=xlAscending, _
    header:=xlNo, Orientation:=xlTopToBottom
    r.Columns(2).Delete xlToLeft
    Application.ScreenUpdating = True
    End Sub
    [/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!