Need to alphabetize data from separate cells within same row
 

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.

Need to alphabetize data from separate cells within same row
 

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---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"marketingchickie" wrote:

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.

Need to alphabetize data from separate cells within same row
 

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

On Mon, 30 Nov 2009 13:26:02 -0800, marketingchickie
wrote:

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.