VBA: I'd like to sort a list of names by surname, but keep the full name in a single cell

[BigShango]

Hi,

Bit of a strange one. I have a list of names (lots of them actually, this is part of a much longer process) that I'd like to sort alphabetically by surname, but I don't want to split the names into two columns. What I need is some way to split the name within the code, sort it, then put it back together before it's actually placed on the sheet.

So that column A goes from:

Alan Smith
James Jackson
Robert Best

To:

Robert Best
James Jackson
Alan Smith

I also need to sort the data in columns B to G along with the names.

I imagine this will be possible but the only issue is that it needs to be fairly quick, as this will happen around 100 times during a process that already takes roughly 10 minutes.

 

[Michael M]

Is there always only 2 names ?
If so use a helper column to extract the surname, then sort that helper column.
It can be deleted when finished !!

 

[Perpa]

Is there always only 2 names ?
If so use a helper column to extract the surname, then sort that helper column.
It can be deleted when finished !!

Big,
Taking it a bit further than Michael has, if your list will be added to and need to be resorted
periodically, you might try the macro code below. For my use, I set it up on an Active X button,
or it could be accessed using Alt+F8, then select the macro from the list provided under
'This Worksheet'. In either case you will have to save the file as a 'macro enabled file' before using.
I am using Windows 7 and Excel 2007. I picked up the following code somewhere on this site...
many thanks to all on this forum who contributed! I use the code every month to sort a list of
members on our list of City volunteers.
Happy Sorting!
Perpa

Sub SortMyList()

'************************Insert code below this line if Active X Button IS used

    'The following code alphbetically sorts on the surname when each 
    'entry is in the format 'name(Space)surname'
    'new names can be added to the bottom of the list and will be sorted

    Dim LastARow As Integer     	'I used column A, change to suit and refelct change below
    Dim rw As Integer
    
    LastARow = Range("A9").End(xlDown).Row		'my data started in Row 9, change to suit

    With Application				'This WITH statement keeps the screen from changing until sorting is done
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    'The folowing sorts columns A
    'My data was in Column A which is column number 1
    'the 'helper column' is Column B which is column number 2

    For rw = 9 To LastARow
        Cells(rw, 2) = Mid(Cells(rw, 1), Application.Find(" ", Cells(rw, 1), 1) + 1, Len(Cells(rw, 1)))   'Note: there is a space between the double quotes
    Next

    Range("A9:B" & LastARow).Select    'This selects the Range to be sorted in the helper column and the original column
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B9:B" & LastARow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort	'Change this to suit if your list is not on Sheet1
        .SetRange Range("A9:B" & LastARow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Range("B9:B" & LastARow).ClearContents	'This removes the helper column info

    Range("C5").Select	'I chose a cell out of the sorted range to end on, change to suit
    
    With Application		'With sorting done let the screen changes be shown
        .ScreenUpdating = True
        .EnableEvents = True
    End With
'************************End of code copy if Active X button is used
 
End Sub