Need help sorting a list of names in VBA to use in a combo box on a user form

[lmoseley7]

I've created a userform that allows people to enter their name, an item description and then they can choose two characteristics of their item based on combo boxes on the userform. I then built another userform to allow for finding previous entries and the user would search on the names that were previously entered. I wanted to have that list of names sorted alphabetically by last name, but the names are entered as one string like:

John Doe
Jane White
Tarzan Smith

To sort in Excel I'd have to delimit the names and touch the data so I was hoping to have VBA break the strings and put them back together and then sort them. This lead me to regular expressions in VBA and I've been able to break out the first and last names with a UDF I wrote. Now I just need to figure out how to take a list of names, have the two functions apply to each name, then take the results of two functions and put the names back together like (last name, first name possible middle initial or middle name) and sort them to be used by the combo box as the list.

The names are in a range named "Entered_Names" on the sheet "Sign-Up List". I'm not sure what to do now that I can break the name segments down. Seems like I need a "For Each" loop looking at the name list, breaking down each name and saving the new combination into a VBA array, then sort that array and populate a combo box with the results. I'm fine with having the results pasted somewhere for reference but I don't think it is necessary for Excel, it would just make validation of the results easier.

Any help would be appreciated and I'm not afraid to read so links are welcome too.

Thanks,

 

[Norie]

I'm confused, you say the names being entered as one string but then you say they are in a range named 'Entered_Names'.

Is that range one cell with the string of names or multiple cells each with a first/last name?

 

[lmoseley7]

The names are in a one column range. Each cell has one name. Basically, the userform allows them to enter their name into a text field, in which people generally enter first and last name in that order. Ultimately I could have them enter first and last in separate text boxes, but I'd still have to combine the results and sort them prior to making them the source for the combo box. I'm also trying to use this as a learning opportunity so I can figure out how useful this might be to me in the future. So using my previous example, imagine the range is cells A1:A3:

A
1 John Doe
2 Jane White
3 Tarzan Smith

I need VBA to look at the names, find the last name and put it before the first and then sort all the entries so that the order would be : John Doe, Tarzan Smith, Jane White in the combo box.

 

[Norie]

Have a look at this, it includes a function that will sort a 2 dimensional array by a specified column.

Sub SortByLastName()
Dim arrIn As Variant
Dim arrOut As Variant
Dim I As Long

    arrIn = Range("A1:A3")

    ReDim Preserve arrIn(1 To UBound(arrIn), 1 To 2)
    
    For I = LBound(arrIn) To UBound(arrIn)
        arrIn(I, 2) = Trim(Split(arrIn(I, 1), " ")(1))
    Next I
    
    arrOut = Sort2DArray(arrIn, 2)
    
    Range("C1:C3").Value = Application.Index(arrOut, , 1)
    
End Sub

Function Sort2DArray(list, Optional SortCol = 1) As Variant

Dim I As Long, J As Long, K As Long
Dim Temp()


    ReDim Temp(1 To UBound(list, 2))

    For I = LBound(list) To UBound(list) - 1
        For J = I + 1 To UBound(list)
            If list(I, SortCol) > list(J, SortCol) Then
                For K = 1 To UBound(list, 2)
                    Temp(K) = list(J, K)
                    list(J, K) = list(I, K)
                    list(I, K) = Temp(K)
                Next K
            End If
        Next J
    Next I

    Sort2DArray = list
    
End Function

 

[lmoseley7]

Have a look at this, it includes a function that will sort a 2 dimensional array by a specified column.

Sub SortByLastName()
Dim arrIn As Variant
Dim arrOut As Variant
Dim I As Long

    arrIn = Range("A1:A3")

    ReDim Preserve arrIn(1 To UBound(arrIn), 1 To 2)
    
    For I = LBound(arrIn) To UBound(arrIn)
        arrIn(I, 2) = Trim(Split(arrIn(I, 1), " ")(1))
    Next I
    
    arrOut = Sort2DArray(arrIn, 2)
    
    Range("C1:C3").Value = Application.Index(arrOut, , 1)
    
End Sub

Function Sort2DArray(list, Optional SortCol = 1) As Variant

Dim I As Long, J As Long, K As Long
Dim Temp()


    ReDim Temp(1 To UBound(list, 2))

    For I = LBound(list) To UBound(list) - 1
        For J = I + 1 To UBound(list)
            If list(I, SortCol) > list(J, SortCol) Then
                For K = 1 To UBound(list, 2)
                    Temp(K) = list(J, K)
                    list(J, K) = list(I, K)
                    list(I, K) = Temp(K)
                Next K
            End If
        Next J
    Next I

    Sort2DArray = list
    
End Function

Wow! That was quick and it works great. Looking over the code it appears that you use split and trim to create the 2 dimensional array and then the function sorts the data. I must be honest, I'm lost in the sort function. Does the ",2" in "(arrIn,2)" and "(list,2)" tell the function to sort the 2nd column of the 2 dimension array? Dropping the values from the macro into cells F1:G3 I see the second column only contains the last name and the first column contains the entire string. That would lead me to believe it sorts last name then the entire string when sorting. When sorting it appears that it compares the "current" (J) and "previous" (I) list items and if the previous is greater than the current, it swaps the order of them in the list?

Thanks so much for your help. What a great Christmas present to me!

 

[Norie]

The 2-dimensional array is originally created from the range of names, i.e. A1:A3.

That is then expanded to add another column for the last name which is populated here.

    For I = LBound(arrIn) To UBound(arrIn)
        arrIn(I, 2) = Trim(Split(arrIn(I, 1), " ")(1))
    Next I

The sort function sorts by a specified column, in this case column 2 - the column with the last name.

In the function UBound(list, 2) etc. gets the upper bound of the 2 dimensional array which represents the number of 'columns' in the array.

When sorting we want to swap entire rows so here we loop through the columns.

For K = 1 To UBound(list, 2)
    Temp(K) = list(J, K)
    list(J, K) = list(I, K)
    list(I, K) = Temp(K)
Next K

Hope that explains things a bit.

 

[lmoseley7]

It's getting clearer...thanks again for the help.