Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    near Boulder, Colorado, USA
    Posts
    112
    Thanks
    22
    Thanked 4 Times in 4 Posts

    How to sort a column based on the last word in each row of that column

    I have a column (A) of 1000 rows of text fragments (one cell per row), and wish to order them according to the last word in each cell.

    If there is more than one word in a cell, the words are space-delimited, with no end punctuation. (Most of these end words are state postal abbreviations, or spelled-out countries.)

    One solution would be to extract the last word and write it to the next column (B), then sort the entire array using B as the key.

    What would be a formula or method to accomplish the building of column B?

    Is there a more efficient way, not requiring the additional column (B)?

    See the example1.xls file attached here.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi cosmlou

    I think you need to use column B as a 'helper' column for what you want.
    But you can always delete that after the sort has been done.

    In my attached file, I have added sample test data to make 1000 rows (to check the solution is reasonably efficient).
    I have added vba code to do the required 'sort-columnA-by-last-word'
    I have added a short-cut-keystroke to execute this sort macro:
    Ctrl-shift-Z will run the macro.

    This uses a function to get the 'last word' of the cell:
    Code:
    Function lastWord(z As String)
    zArr = Split(Trim(z), " ")
    lastWord = zArr(UBound(zArr))
    End Function
    The routine to do the sort is:
    Code:
    Sub sortColALastWord()
    
    zLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    temp = "b1:b" & zLastRow
    [b1] = "=lastWord(a1)"
    
    [b1].Copy Range(temp)
    Range(temp) = Range(temp).Value
    [b1].CurrentRegion.Sort key1:=[b1]
    Range(temp).Clear
    
    End Sub
    It seems to work very fast on my laptop.
    There are always other ways of doing this of course!

    zeddy
    '
    Attached Files Attached Files

  3. The Following User Says Thank You to zeddy For This Useful Post:

    cosmlou (2016-07-28)

  4. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    near Boulder, Colorado, USA
    Posts
    112
    Thanks
    22
    Thanked 4 Times in 4 Posts
    Many thanks for the prompt response. -Collier

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts

    A amed range and an array formula

    Cosmlou,

    Using your sample spreadsheet, to find the last word, in cell B1 place the following formula then copy down

    In cell B1 then copy down
    Code:
    =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
    Name the range of values in column B, "List" (without the quotes)

    To sort the named range "List", place the following formula in C1

    In cell C1:
    Code:
    =INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))
    Make the formula an array formula by placing the cursor anywhere in the formula then pressing Ctrl+Shift+Enter then release all keys. The formula in C1 will look like this:
    Code:
    {=INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))}
    Copy down and your last words will be sorted. Hide column B if you like. Source of formulas found here

    http://www.mrexcel.com/forum/excel-q...ml#post3486280
    http://www.get-digital-help.com/2009...array-formula/

    HTH,
    Maud
    Attached Files Attached Files

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    cosmlou (2016-07-30)

Tags for this Thread

Posting Permissions

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