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

Question

question

TylerPhillips-3393 asked Apr 6, '22 TylerPhillips-3393 commented Apr 8, '22

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

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.

office-vba-dev

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomJebo-9587 · Apr 07 at 04:00 PM

@TylerPhillips-3393,

I've changed the tag to [office-vba-dev] since this is a question about VBA development.

Best regards,
Tom Jebo
Sr Escalation Engineer
Microsoft Open Specifications Support

0 ·

Answer 1 · 2022-04-07T17:37:32.893Z

Viorel-1 answered Apr 7, '22 TylerPhillips-3393 commented Apr 8, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TylerPhillips-3393 · Apr 08 at 01:37 PM

This is what I asked for! The only thing I had to do was Dim a As Variant. Works likes a charm!

0 ·

Answer 2 · 2022-04-07T18:52:36.353Z

GerardWertheimer-2426 answered Apr 7, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question