VBA Excel: How to Sort Alphabeltically and Delete Duplicate Values in a String?

Question

I have an Excel sheet with premade values. For instance A1 = "A09D, B04B, Y02B" and A2 = "A09D, W03, E02" & B1="Tyler" and B2 = "Phillips" I have a ComboBox with both B1 and B2 Values. If I select B1 it takes the values of A1 and puts it in a TextBox to display. Same goes for B2 -> A2-> Outputs to TextBox. For big picture, this excel sheet has values for A1-A72 and B1-B72. I have Ten ComboBoxes that has B1-B72 in which you may select. Depending on the ComboBox Number, it will output to that corresponding TextBox. So If I select B30 in ComboBox#6 Then it will take A30 and Outputs to Textbox#6 to display what was selected.

My next step is to take every filled TextBox and combine the values into a string. So if TextBox#1 = "A09D, B04B, Y02B" and TextBox#2 = "A09D, W03, E02" , the string will be "A09D, B04B, Y02B, A09D, W03, E02". I got this value by having an collection and if the textbox IS NOT empty then it would add it to the collection and I used a For Loop from i to Collection.Count to display values of the NOT Empty TextBoxes.

This is where my problem is. I can't figure out how to Sort the String Alphabetically and Delete Duplicate Values in a String. So if my String was "A09D, B04B, Y02B, A09D, W03, E02" Then I would need it to be outputted as a string that is "A09D, B04B, E02, W03, Y02B". This all would be done on a CommandButton_Click() Event.

If anyone has any solutions, I would be very grateful.

Answer 1

Check an example:

Dim myString As String
myString = "A09D, B04B, Y02B, A09D, W03, E02"


' split '

Dim a
a = Split(myString, ",")

For i = LBound(a) To UBound(a)
    a(i) = Trim(a(i))
Next i

' sort '

For i = LBound(a) To UBound(a) - 1
    For j = i + 1 To UBound(a)
        If a(i) > a(j) Then
            Dim t
            t = a(i)
            a(i) = a(j)
            a(j) = t
        End If
    Next j
Next i

' remove duplicates '

i = LBound(a)
For j = i + 1 To UBound(a)
    If a(j) <> a(i) Then
        i = i + 1
        a(i) = a(j)
    End If
Next

ReDim Preserve a(i)

' join '

Dim result As String
result = ""

For i = LBound(a) To UBound(a)
    If result <> "" Then result = result & ", "
    result = result & a(i)
Next

MsgBox result

Improve it to work in case of empty string.

Answer 2

Thanks for sharing. It's really helpful for my current task Technoblade merch store. I am happy to view this type of logic.