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

Tyler Phillips 21 Reputation points
2022-04-06T20:49:54.247+00:00

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.

{count} votes

Accepted answer
  1. Viorel 113.8K Reputation points
    2022-04-07T17:37:32.893+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Gerard Wertheimer 1 Reputation point
    2022-04-07T18:52:36.353+00:00

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

    0 comments No comments