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.

 

[shknbk2]

Based on https://exceljet.net/formula/sort-text-and-numbers-with-formula

Use F2-J2 as helper cells. In F2, put this formula and copy through to J2 (i.e., drag lower right corner to fill across):

=COUNTIF($A2:$E2,"<="&A2)

You can then hide columns F to J if you want after setting up the whole sheet.

In K1 (and copying through to O2:

=IFERROR(INDEX($A2:$E2,MATCH(COLUMNS($K$2:K2),$F2:$J2,0)),"")

From here, you can copy columns F:O down.

 

[Peter_SSs]

Welcome to the MrExcel Board!

Another way would be to use a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across as far as you might need and down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

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

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Larry	Bobby	Erin	Kim	Sam		Bobby	Erin	Kim	Larry	Sam
3	Ken	Tim					Ken	Tim
4	Deanne	Dean	Denise	Deano			Dean	Deanne	Deano	Denise
5
Sort

 

[londonparisrome]

Thank you so much! This worked great! I had an issue when there was only one student in the family, but a simple "if" condition on student 2 solved the problem.

I have self-taught myself Excel, and this one stumped me. I appreciate everyone's help!

 

[Peter_SSs]

This worked great!

Out of interest, does that refer to the helper column approach or the UDF or both?

 

[londonparisrome]

Out of interest, does that refer to the helper column approach or the UDF or both?

I used your code, Peter. I didn't test the other approach since yours worked on the first try. Thank you!

 

[Peter_SSs]

Cheers. Thanks for the clarification. Glad it worked for you.

 

[londonparisrome]

Based on https://exceljet.net/formula/sort-text-and-numbers-with-formula

Use F2-J2 as helper cells. In F2, put this formula and copy through to J2 (i.e., drag lower right corner to fill across):

=COUNTIF($A2:$E2,"<="&A2)

You can then hide columns F to J if you want after setting up the whole sheet.

In K1 (and copying through to O2:

=IFERROR(INDEX($A2:$E2,MATCH(COLUMNS($K$2:K2),$F2:$J2,0)),"")

From here, you can copy columns F:O down.

How would you allow for blank fields on this formula? I have some families with only one child, so it is assigning that child as 5th in the sort order.

 

[londonparisrome]

I used your code, Peter. I didn't test the other approach since yours worked on the first try. Thank you!

Now I'm getting a #NAME ? error. Any suggestions?

 

[londonparisrome]

I solved it. I counted the number of fields with values in column P, and then used the following formula. There may be an easier way, but it worked.

IF($P2=1,A2,IF($P2=2,COUNTIF($A2:$B2,"<="&A2),IF($P2=3,COUNTIF($A2:$C2,"<="&A2),IF($P2=4,COUNTIF($A2:$D2,"<="&A2),IF($P2=5,COUNTIF($A2:$E2,"<="&A2))))))