1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

need to sort in excel

Discussion in 'BlackHat Lounge' started by theseodude, Aug 1, 2013.

  1. theseodude

    theseodude Regular Member

    Joined:
    Jun 25, 2012
    Messages:
    303
    Likes Received:
    88
    I have 2 columns in excel. column A is words, column B is numbers.


    So for example:
    apple 6
    rain 6
    apple 4
    laptop 2
    coffee 5
    coffee 8


    I need to sort column A in alphabetical order while also sorting by the numbers in column B.
    so for the above list, the right order would be
    apple 6
    apple 4
    coffee 8
    coffee 5
    laptop 2
    rain 6

    This is a huge list and cannot be done by hand. How can I do it automatically? I have SB, if necessary.
     
  2. mrankin

    mrankin Jr. VIP Jr. VIP Premium Member

    Joined:
    Oct 17, 2008
    Messages:
    1,215
    Likes Received:
    571
    Location:
    Australia
    Home Page:
    Just highlight both columns in Excel and then sort A-Z (works using Excel 2007)

    Edit - sorry, I re-read your post and realise you want the word in order, but the numbers in reverse order (according to your post). To do this, Click on Sort and & Filter, then Custom Sort.

    Start with Column A and choose the order you want it sorted in. Then click Add Level, and sort Column B and again sort in the order you want (Z to A for that one from what I can tell).
     
  3. theseodude

    theseodude Regular Member

    Joined:
    Jun 25, 2012
    Messages:
    303
    Likes Received:
    88
    This does not sort the numbers right. I need the numbers high to low, just like the example above.
     
  4. mrankin

    mrankin Jr. VIP Jr. VIP Premium Member

    Joined:
    Oct 17, 2008
    Messages:
    1,215
    Likes Received:
    571
    Location:
    Australia
    Home Page:
    Gottcha - edited my post above.
     
    • Thanks Thanks x 1
  5. theseodude

    theseodude Regular Member

    Joined:
    Jun 25, 2012
    Messages:
    303
    Likes Received:
    88
    thanks. your solution is great and (given my problem), it is correct.
    but after using your solution, I realized my question was not complete enough.
    alright, I won't try to simplify things.
    I have a list of domains in one column and their pr in the next colum. like
    aaa.com/128.html PR1
    aaa.com/234.html PR4
    bbb.com/134.html PR5
    aaa.com/424.html PR2
    bbb.com/486.html PR4
    ccc.com/556.html PR1
    ddd.com/124.html PR2

    I want each unique domain to be sorted according to PR, form high PR to low PR. like this
    aaa.com/234.html PR4
    aaa.com/424.html PR2
    aaa.com/128.html PR1
    bbb.com/134.html PR5
    bbb.com/486.html PR4
    ccc.com/556.html PR1
    ddd.com/124.html PR2

    the proposed solution almost works, except it fails because of the parts after the slashes....because sorting treats aaa.com/123.html different from aaa.com/233.html....should treat them the same for it to work perfectly.
     
  6. srb888

    srb888 Elite Member

    Joined:
    Jul 30, 2008
    Messages:
    3,260
    Likes Received:
    5,067
    Gender:
    Male
    Occupation:
    WebzSurfer
    Location:
    Sun, Mon, Tue, WTF, Sat!!! :)
    OP, he is right...

    Like this:

    [​IMG]

    p.s.:

    Sorry! Didn't read your next post, was busy creating that image while you posted it...
     
    • Thanks Thanks x 1
    Last edited: Aug 1, 2013
  7. mrankin

    mrankin Jr. VIP Jr. VIP Premium Member

    Joined:
    Oct 17, 2008
    Messages:
    1,215
    Likes Received:
    571
    Location:
    Australia
    Home Page:
    If you want Excel to sort column A on the domain only, then you need to strip out the pages. You can either do this in SB, or use Data - Text to columns with "/" as the delimiter. Excel cannot sort on the partial contents of a cell that I know of.
     
    • Thanks Thanks x 1
  8. srb888

    srb888 Elite Member

    Joined:
    Jul 30, 2008
    Messages:
    3,260
    Likes Received:
    5,067
    Gender:
    Male
    Occupation:
    WebzSurfer
    Location:
    Sun, Mon, Tue, WTF, Sat!!! :)
    sorry **deleted**
     
    Last edited: Aug 1, 2013
  9. srb888

    srb888 Elite Member

    Joined:
    Jul 30, 2008
    Messages:
    3,260
    Likes Received:
    5,067
    Gender:
    Male
    Occupation:
    WebzSurfer
    Location:
    Sun, Mon, Tue, WTF, Sat!!! :)
    OK: I think this is your solution (Add those extra cells, and fill in those formulas for all cells, you can do it automatically once you fill up the first row). Then sort cells as shown in figure.

    [​IMG]


    (ref.: Cell B12):
    Cell C formula has a small error (came from earlier tests): It should be:

    =Find("/",A1)
    Anyway, that part is redundant (not required) now, because I have included that formula in other 2 cells.



    HTH
     
    • Thanks Thanks x 1
    Last edited: Aug 1, 2013
  10. srb888

    srb888 Elite Member

    Joined:
    Jul 30, 2008
    Messages:
    3,260
    Likes Received:
    5,067
    Gender:
    Male
    Occupation:
    WebzSurfer
    Location:
    Sun, Mon, Tue, WTF, Sat!!! :)
    OK dude, it is done (see the above post), hopefully the way you want it to be. :D