Automatically Sort Fields Across a Row: Is there a formula?

[londonparisrome]

I am working on a solution for our school district PTA. I have 5 student names on one "family" record row, with hundreds of rows in the sheet. I would like to sort the student names alphabetically, and most people have listed the students in grade order.

For instance, the record may have cells A2-E2 with Larry, Bobby, Erin, Kim, Sam, and I need it to automatically sort to Bobby, Erin, Kim, Larry and Sam in columns F2-J2.

Is that possible? The end users of the spreadsheet I am creating are not very tech-savvy, so I was hoping to have am automated process.

 

[Peter_SSs]

Now I'm getting a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NAME]#NAME [/URL] ? error. Any suggestions?

If you are getting that error using my UDF it means that you have made a spelling/typing error in the code or the function name in the worksheet. In my code, the function name was
SortedNames
In the worksheet, if you accidentally wrote, say
=SrotedNames($A2:$E2,COLUMNS($G2:G2))
then you would get a #NAME ? error

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
2	Larry	Bobby	Erin	Kim	Sam		#NAME ?	Erin	Kim	Larry	Sam
Sort

The other way that you could get a #NAME? error is that you have not saved the file as a macro-enabled file (*.xlsm) and so the function code would have been deleted from the workbook.

 

[Tom.Jones]

For me that UDF don't work. Get #value

Can't upload an image.

 

[Tom.Jones]

Can't upload an image.

 

[Peter_SSs]

For me that UDF don't work. Get #value

Well, you can see from posts 3 & 6 that has worked for me and for londonparisrome.
If you wanted to try to get it to work for your situation, you would need to explain/show exactly what you have, where and exactly what formula you used. Have you tried setting up a fresh workbook with the code installed and layout & data exactly the same as post 3 to see if you can replicate those results?

Also, what version of Excel and what operating system are you working with?

Can't upload an image.

Do you mean that you are not allowed to perhaps due to some workplace restrictions or do you mean you haven't worked out how to do it yet? My signature block below has a link with help to enable worksheet & formula information to be posted - like in post 3 for example.

 

[Tom.Jones]

I used example in your post #3 with that UDF and get #value
I use office 365 64bit.
Tried many time nd get all tie #value . No mistake with name of UDF.

My system is problem with image. Don't leave me to upload.

 

[Peter_SSs]

I suspect that you are not actually using the example in post 3 but either ...
- you have the same name/word in more than one cell in the row. (The OP in this thread has children's name from a particular family in each row so would not have duplicates, hence I didn't allow for that.) , or
- you have formulas in columns A:E that can return "" and 2 or more cells in the row contain "", or
- you have formulas in columns A:E and at least one of those formulas is returning a #VALUE ! error

My system is problem with image. Don't leave me to upload.

Which one of the methods suggested did you try?
What was the error message when you tried to post an image?

An alternative to uploading an image to your post is to upload a dummy sample file to a public file-share site and provide a link to that file.

 

[Tom.Jones]

I used your UDF with example in post #3

Larry	Bobby	Erin	Kim	Sam		#value
Ken	Tim					#value
Deanne	Dean	Denise	Deano			#value

<tbody>
</tbody>

with this UDF:

[FONT=Verdana]Function SortedNames(rng As Range, num As Long) As String
  Dim SL As Object
  Dim r As Range
  
  Set SL = CreateObject("System.Collections.Sortedlist")
  For Each r In rng
    If Not IsEmpty(r.Value) Then SL.Add r.Value, Empty
  Next r
  If num <= SL.Count Then SortedNames = SL.GetKey(num - 1)
End Function[/FONT]

Where I'm wrong?

 

[Peter_SSs]

I used your UDF with example in post #3

Where I'm wrong?

I assume what you have shown is A2:G4?
If so, can you select cell G2 and copy the formula from the formula bar and post it here?

 

[Tom.Jones]

My data is in Range A2:E4 (like in post #17 )
Formula in G2 =SortedNames($A2:$E2;COLUMNS($G2:G2))

I use in formula ";" not ","

 

[Peter_SSs]

My data is in Range A2:E4 (like in post #17 )
Formula in G2 =SortedNames($A2:$E2;COLUMNS($G2:G2))

I use in formula ";" not ","

Translate the English function COLUMNS() into your local Excel version language?