Need to Sort String of Letters in Cell

[rwmill9716]

C column has cells filled with letters beginning in Cell C8. Each of these is combined with the letter combination in Cell D7 (e.g., C9&D$7) to form the combinations beginning in Cell D9 and following below in Col D. I need to sort those letters within each cell into Col E, then eliminate the letters that appear twice (Max possible) and place the remaining in Col F.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Design Generators</td><td style="text-align: center;border-top: 1px solid black;;">ABD</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-left: 1px solid black;;">ABD</td><td style="text-align: center;background-color: #000000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-left: 1px solid black;;">ACE</td><td style="text-align: center;;">ABDACE</td><td style="text-align: center;;">AABCDE</td><td style="text-align: center;border-right: 1px solid black;;">BCDE</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-left: 1px solid black;;">BCF</td><td style="text-align: center;;">ABDBCF</td><td style="text-align: center;;">ABBCDF</td><td style="text-align: center;border-right: 1px solid black;;">ACDF</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;">ABCG</td><td style="text-align: center;border-bottom: 1px solid black;;">ABDABCG</td><td style="text-align: center;border-bottom: 1px solid black;;">AABBCDG</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;">CDG</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Thanks for your help.

Ric

 

[rwmill9716]

Let me simplify this request. How do I sort a string of letters (variable length) within a cell?

For example, I need 'ABCGAG' to be sorted into 'AABCGG'

 

[shg]

UDF?

Function StrSort(sInp As String, _
                 Optional bAsc As Boolean = True, _
                 Optional bCaseSens As Boolean = False) As String
    ' shg 2008, 2011
    ' UDF or VBA
    ' Insertion-sorts sInp
    Dim i           As Long
    Dim j           As Long
    Dim s           As String
    Dim iComp       As vbCompareMethod
    Dim iSign       As Long
 
    StrSort = sInp
    iComp = IIf(bCaseSens, vbBinaryCompare, vbTextCompare)
    iSign = IIf(bAsc, 1, -1)
 
    For i = 2 To Len(sInp)
        s = Mid(StrSort, i, 1)
        For j = i - 1 To 1 Step -1
            If StrComp(Mid(StrSort, j, 1), s, iComp) <> iSign Then Exit For
            Mid(StrSort, j + 1) = Mid(StrSort, j, 1)
        Next j
        Mid(StrSort, j + 1) = s
    Next i
End Function

E.g., =StrSort(A1)

 

[shg]

Or to sort and eliminate letters that are duplicated,

Function StrSort2(sInp As String) As String
    Dim i           As Long         ' index to string characters
    Dim aiNum(0 To 255) As Long     ' count of characters by code
    Dim iAsc        As Long         ' index to aiNum
    For i = 1 To Len(sInp)
        iAsc = Asc(Mid(sInp, i, 1))
        aiNum(iAsc) = aiNum(iAsc) + 1
    Next i
    For iAsc = 0 To 255
        If aiNum(iAsc) = 1 Then StrSort2 = StrSort2 & Chr$(iAsc)
    Next iAsc
End Function

 

[rwmill9716]

Thanks, SHG, this function works well to sort the string of letters.

Is it now possible to take this sorted string and eliminate letters that are
there twice? Note, only single and double letter combinations can occur.

For example, with your function, I can take 'A1: ABDBCF' to 'B1: ABBCDF' or 'A2: ABDABCG' to 'B2: AABBCDG' I now need to take these to 'ACDF' and 'CDG' in cells C1 and C2.

Thanks Again,

Ric

 

[shg]

If yer waitin' for me, yer goin' backwards ...

 

[rwmill9716]

SHG,

StrSort(A1) sorts the string into B1, but StrSort(B1) doesn't eliminate
double letters; it just resorts B1.

For Examples,

a1: ABDABEF B1: AABBDEF C1: ABEFABD

Instead, C1 should be 'DEF'

 

[mirabeau]

Put a string of capital letters you want sorted and duplicates removed in cell A1.
Run this code and see if it's your sort of thing. Result in C1.

Sub test()
Dim a As Variant
Dim i&, j&
Dim b(65 To 90) As Boolean
Dim k As String
a = Range("A1").Value
For j = 1 To Len(a): For i = 65 To 97
    If Chr(i) = Mid(a, j, 1) Then b(i) = True
Next i, j
For i = 65 To 90
    If b(i) Then k = k & Chr(i)
Next i
Range("C1") = k
End Sub

 

[rwmill9716]

This sub gets rid of the 2nd member but leaves the first so 'ABDABCEFG' in A1 puts
'ABCDEFG' in C1. I need C1 to equal 'CDEFG'. i.e., get rid of both As and both Bs. I'm also
going to have ~100 of these in Col A that I would like to convert to Col C.

 

[Rick Rothstein]

Are you sorting the letters only in order to be able to identify the duplicated letters? If so, then this function (UDF) will remove the duplicated letters without sorting the letters...

Function RemoveDupes(ByVal S As String) As String
  Dim X As Long
  For X = 65 To 90
    If UBound(Split(S, Chr(X))) > 1 Then S = Replace(S, Chr(X), "")
  Next
  RemoveDupes = S
End Function