Tricking Sort Logic

[Odyrus]

Good day all,

I have an on-open macro that auto sorts 2 fields in my data table. I'm running into some issues wit the sort on the second field and am looking for a creative, non-tedious solution.

Here is the issue:

The first field that's sorted contains either one or two names, for example Smith or Smith, Jones.

The second field that's sorted contains a number, 1 or 2.

If there are multiple Smiths with a few Smith, Jones the second sort field looks at the Smiths as if they are separate from the Smith, Jones, as I suppose the sorting logic should do. How do I suppress this so that if all the Smiths are grouped together, including some Smith, Jones, it will sort the second column as if all the Smiths were one group?

Hopefully that's not confusing. Is this possible without laborious work?

Appreciate any advice!
Cheers!

[p45cal]

Assuming you essentially want to sort on the names column only to the first comma, then try something along these lines (I've assumed excel 2007 upwards, too):
With ActiveSheet
Set Namescolumn = .Columns(4) 'adjust to actual column no.
Set NosColumn = .Columns(6) 'adjust to actual column no.
Namescolumn.Offset(, 1).Insert
Namescolumn.TextToColumns Destination:=Namescolumn.Cells(1).Offset(, 1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 9), _
Array(3, 9), Array(4, 9), Array(5, 9), Array(6, 9), Array(7, 9)), _
TrailingMinusNumbers:=True
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Namescolumn.Cells(1).Offset(, 1), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=NosColumn.Cells(1), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A1:I11") 'adjust to your existing macro's sort range
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Namescolumn.Offset(,1).Delete

[CatDaddy]

sort the second column first, (1111111122222222) then the numbers will line up with your names (johnjohnjohnjohnmikemikemikemike, 111222111222) you know?

[Odyrus]

Thanks for both suggestions, I'll give them a try.