Sorting names by Surname Alphabetically without using two columns

I've been looking online for a solution to sorting names alphabetically using the surname but all the solutions come via using two columns, one for the first name, one for the surname. I wanted to avoid that as when published to a webpage it is not very attractive. At the moment I have the names listed Adams. Brian, Adams. Linda, Brown, Dave, etc because they normally are used alphabetically but sometimes when you are doing result tables the names move up and down. I would however like to exhibit the names as Brian Adams, Linda Adams, Dave Brown, etc because I think they are aesthetically better. Especially when in a pair or a team. Brian Adams & Dave Brown looks so much better than Adams. Brian & Brown. Dave. Anyway the solution I came up with is to number the names Alphabetically in a hidden preceding column and then when sorting by say points, highest points first, then the number column representing the name. This works, but I just wondered if there were a better way to go. For a start the If statement would be very long if you had 50 names, one of my charts has 150. I currently have =IFS(I7=1,$G$7,I7=2,$G$8,I7=3,$G$9) and that's just for 3 names. Maybe the solution is the right way to go but there is a better formula to use or there is a better solution altogether?

I've attached a workbook as an example, in case you can't picture what I'm asking. Example 4 is the solution, sorting by K and then I. I'd use VBA to update each time data was added.

Thank you.

I'm having difficulty making out your sort order. this... =TRIM(RIGHT(B7,LEN(B7)-FIND(".",B7))&" "&LEFT(B7,FIND(".",B7)-1)) gets rid of the . between last name and first and rearranges them to first name and last name. Sorting however becomes a different issue. Initially it appears (col J) that your sort is alphabetical by first name descending since last name Pym would be above McKenzie in a descending order. But in a descending order Brian would be above Benjamin but you have them reversed which makes it appear that if they begin with the same letter for the first name then you want the last name to take precedence?
This formula =SORT(G7:G9,,-1,FALSE) should produce what you want except that Ben and Brian are reversed. Maybe you need to clarify what your sort order rules are a bit more.

YOu can sort the names like this:

=LET(s,LEFT(B7:B9,FIND(".",B7:B9)-1),f,MID(B7:B9,FIND(".",B7:B9)+1,99),SORTBY(TRIM(f&" "&s),s,1,f,1))

I am unclear as to which column contains the points and how they are derived.

This will do the whole lot:

=LET(a,B7:C9,s,LEFT(B7:B9,FIND(".",B7:B9)-1),f,MID(B7:B9,FIND(".",B7:B9)+1,99),p,C7:C9,x,SORT(p,,-1),y,SORTBY(TRIM(f&" "&s),p,-1,s,1,f,1),CHOOSE({1,2},y,x))

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	B	C	D	E	F
6	Name	Points
7	Adams. Brian	10		Ian McKenzie	30
8	McKenzie. Ian	30		Benjamin Pym	20
9	Pym.Benjamin	20		Brian Adams	10

Sheet: Sheet1

Oh, dear ... How many times do we have to ask you to provide realistic sample data??? It's SOOOOO frustrating when you come back with a response like that!

Did you look at the solution offered in post #5?

I've replicated the formula to match the data layout described:

=LET(s,LEFT(B3:B5,FIND(".",B3:B5)-1),f,MID(B3:B5,FIND(".",B3:B5)+1,99),p,Q3:Q5,x,SORT(p,,-1),y,SORTBY(TRIM(f&" "&s),p,-1,s,1,f,1),CHOOSE({1,2},y,x))

Just adapt the length of the B and Q ranges to suit:

=LET(s,LEFT(B3:B152,FIND(".",B3:B152)-1),f,MID(B3:B152,FIND(".",B3:B152)+1,99),p,Q3:Q152,x,SORT(p,,-1),y,SORTBY(TRIM(f&" "&s),p,-1,s,1,f,1),CHOOSE({1,2},y,x))

Marvo - all you have to do is REMOVE the sensitve stuff, but leave the columns where they are. It really isn't hard.

Once again - have you looked at my solutions, or am I, for want of a better expression, pissing into the wind???

I don't need to see your website, thanks - I need you to focus on this thread.

PS I just looked - I used to do web design ...

PPS I see you ARE looking at my suggestion - good. Let me know how you get on.

Oh, I'm sorry to hear this. Get well soon!

I haven't been ill since I retired from teaching - hotbeds of nasties, schools!

Totally lost. What do you expect to see? Where do you expect to see it?

Maybe this?

=LET(f,LEFT(B15:B22,FIND(" ",B15:B22)-1),s,MID(B15:B22,FIND(" ",B15:B22)+1,99),SORTBY(CHOOSE({1,2},B15:B22,Q15:Q22),Q15:Q22,-1,s,1,f,1))

Really confusing ...

Heading into the garden now.