Need to alphabetize data from separate cells within same row

[marketingchickie]

Hi there,

I have a worksheet with 6,500 rows of data. In each row, there are up to 6
cells containing a name in "Last, First" format. I need to alphabetize the
data from these 6 cells so they go across the row in order left to right,
alpha by last name.

Example of my data layout:

Column 1 Column 2 Column 3
Row 1 Jackson, Bob Anderson, Lisa Cardinal, Mike
Row 2 Tomlinson, Steve Dodge, Sarah White, Brian


I need them to be in this order:

Column 1 Column 2 Column 3
Row 1 Anderson, Lisa Cardinal, Mike Jackson, Bob
Row 2 Dodge, Sarah Tomlinson, Steve White, Brian


Is there a formula I can apply that will pull from only the portion of the
data before the comma in each cell? Or do I need to separate out all of the
names so the first and last names are in separate columns for this to work?

Please help! Thank you.

 

[ryguy7272]

Not sure what happened to a couple of those first names, but try this:
Select all > Data > Sort > Options > Left to Right > OK > OK

HTH,
Ryan---

 

[Gord Dibben]

Sub SortRows()
'Tom Ogilvy macro
Dim r As Long
Dim lRow As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Make the r = 1 whatever the first row of data you want to sort on is.
'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust as
necessary
'The resize(1, 7) expands the range to 1 cell deep by 7 cells wide

For r = 1 To lRow
With Cells(r, 1).Resize(1, 7)
.Sort Key1:=Cells(r, 2), Order1:=xlAscending, Header:=xlGuess, _
Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
End With
Next r

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Gord Dibben MS Excel MVP