Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Buenos Aires, Argentina
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort a list for the size of each row

    Hi

    I want to sort a list of Excel 2003 for the size of each row. For example:

    A column like this:

    aa
    aaaa
    a
    aaaaaa
    aaaa
    aaaaaaaaaaa


    Has to be like this:

    aaaaaaaaaaa
    aaaaaa
    aaaa
    aaaa
    aa
    a


    Is possible with a Macro or a formula?

    Thanks

  2. #2
    4 Star Lounger SpywareDr's Avatar
    Join Date
    Dec 2009
    Location
    Riviera Beach, Maryland, USA
    Posts
    492
    Thanks
    10
    Thanked 52 Times in 43 Posts
    If those are in cells A1 through A6, then:

    1. Put the following formula in B1

      =LEN(A1)

    2. Then copy that formula in B1 to B2 down through B6

    3. Now select A1 through B6, right-click it and select "Sort>Z-A"
    .

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Any reason you could not just select the range A1 through A6 then sort Z-A without using formulas in the adjacent column?

    Simply recording a macro would have given you the following:

    Code:
    Sub Macro1()
        Range("A1:A6").Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:A6")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    Cleaning it up a bit would have yielded:
    Code:
    Sub Macro1()
        With ActiveSheet.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlDescending
            .SetRange Range("A1:A6")
            .Header = xlNo
            .Apply
        End With
    End Sub
    Last edited by Maudibe; 2013-11-24 at 12:40. Reason: spelling corrections

  4. #4
    4 Star Lounger SpywareDr's Avatar
    Join Date
    Dec 2009
    Location
    Riviera Beach, Maryland, USA
    Posts
    492
    Thanks
    10
    Thanked 52 Times in 43 Posts
    Quote Originally Posted by Maudibe View Post

    Any reason you could not just select the range A1 through A6 then sort Z-A without using formulas in the adjacent column?
    Yes that could be done if all of the characters are exactly the same.

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Buenos Aires, Argentina
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It doesn't work any of the three methods.

    I have to be more specific. The lists consist of emails.

    Attached is a list of emails to try. But the actual lists are 50,000+ emails.
    Last edited by ruirib; 2013-11-27 at 20:18. Reason: Removed emails list

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    SpywareDr's method works for me with your sample dataset.
    Make sure you have the entire range set
    Sort on B column largest to smallest...

    Steve

  7. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Buenos Aires, Argentina
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    SpywareDr's method works for me with your sample dataset.
    Make sure you have the entire range set
    Sort on B column largest to smallest...

    Steve
    When I right-click there is no a Sort command...

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    There should be one in the menu. If there is none in the right-click menu, do you have code running in the background?

    As I mentioned before, it works when I import your text file into excel.

    Steve

  9. #9
    Star Lounger
    Join Date
    Dec 2009
    Location
    Buenos Aires, Argentina
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is a picture of the Excel, and I don't have any code
    Last edited by macropod; 2013-11-27 at 23:28. Reason: email list image deleted

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Look in the menu under datos [In English I think it was under Data-sort, the menus have changed in the newer versions, I don't recall if Sort was in the right-click menu of the older versions]
    Also What is your formula in B? It does not seem to be correct (you have a Name error). Does your version have an alternate name for the LENgth...

    Steve

  11. #11
    Star Lounger
    Join Date
    Dec 2009
    Location
    Buenos Aires, Argentina
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is another capture with what you were asking.
    I don't know about formulas and how to search for an alternate LENgth...
    Last edited by macropod; 2013-11-27 at 23:29. Reason: email list image deleted

  12. #12
    4 Star Lounger SpywareDr's Avatar
    Join Date
    Dec 2009
    Location
    Riviera Beach, Maryland, USA
    Posts
    492
    Thanks
    10
    Thanked 52 Times in 43 Posts
    In Spanish I believe the forumla in B1 should be:

    =LARGO(A1)

    Now copy that all the way down your B column.

    Then to sort, try this:

    1. Click whatever data is in cell A1 once, then press [Ctrl][A] to highlight all of your data.

    2. Click the "Data" tab at the top, then in the "Sort & Filter" area click the "Sort" icon.

    3. In the window that pops up, change the "Sort by" to "Column B", change "Sort On" to "Values" and the "Order" to "Largest to Smallest", and click OK.


    --

    FWIW: Here's the English / Spanish Excel 2007 Funtion name page I used to find "largo".

  13. #13
    Star Lounger
    Join Date
    Dec 2009
    Location
    Buenos Aires, Argentina
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes. It's working

    Many thanks.

  14. #14
    4 Star Lounger SpywareDr's Avatar
    Join Date
    Dec 2009
    Location
    Riviera Beach, Maryland, USA
    Posts
    492
    Thanks
    10
    Thanked 52 Times in 43 Posts
    You're welcome.

  15. #15
    Star Lounger
    Join Date
    Jun 2012
    Posts
    61
    Thanks
    5
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by didadocom View Post
    Attached is a list of emails to try. But the actual lists are 50,000+ emails.
    didadocom,

    May I suggest removing the attachment now that your problem is resolved? I'm assuming all the emails in your "emails.txt" are valid, in which case any forum visitor who might be a spammer would love to get hold of list like that.

    Just a thought

    Cheers...

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
  •