CODEWITHSUNDEEP
excelvbasorting
Shin
Shin

Reputation: 33

Sorting the order of contents in a cell

As the title suggests, I was wondering if it were possible to sort the order of contents with in a cell according to the text. For example, some cells that contain the text "4C + 5C + BBU/RRH + 4T4R" that have a specific complexity number associated with it. The problem is to vlookup that specific complexity number I need to have the text within the cell in that exact order for it to match. I have this jumbled order for several other iterations (e.g. "4C + 2nd RRH + 3C + BBU/RRH" should read "2C + 3C + 2nd RRH + BBU/RRH"). If given the correct order of preference if it exists in the cell would it be possible to correct the order of contents in the cell? Here is the associated preference list:

  1. 2C
  2. 3C
  3. 4C
  4. 5C
  5. 6C
  6. 7C
  7. 2nd RRH
  8. RRH ADD
  9. BBU/RRH
  10. XMU/RRH
  11. 4T4R

Such that the cell should be able to form a ranking system of what should go first, second, third, etc. if it exists in the cell (e.g. should be able to take "7C + 3C + 4T4R + RRH ADD" into "3C + 7C + RRH ADD + 4T4R" just as "4C + 3C + 2C" should read "2C + 3C + 4C"). I'd like to be able to apply this formula over a column AU using VBA if possible. Thanks in advance, I haven't seen anything quite like this issue, so any insight would be go a long way.

Upvotes: 0

Views: 88

Answers (1)

Miguel_Ryu
Miguel_Ryu

Reputation: 1418

You can check the original string for the existence of the values in an ordered way and construct a new string, as below:

Function customSort(ByVal original As String) As String

    Dim SortBy As Variant
    Dim n As Double
    Dim strReturn As String

    SortBy = Array("2C", _
                   "3C", _
                   "4C", _
                   "5C", _
                   "6C", _
                   "7C", _
                   "2nd RRH", _
                   "RRH ADD", _
                   "BBU/RRH", _
                   "XMU/RRH", _
                   "4T4R")


    'for each in SortBy array
    For n = 0 To UBound(SortBy)
        'loop from 1 to count of occurrence in string
        '(Len(original) - Len(Replace$(original, SortBy(n), ""))) / Len(SortBy(n)) counts occurrences in string, case sensitive
        For i = 1 To (Len(original) - Len(Replace$(original, SortBy(n), ""))) / Len(SortBy(n))
            strReturn = strReturn & SortBy(n) & " + "
        Next
    Next

    customSort = Left(strReturn, Len(strReturn) - 3)

End Function

You can either call this function from a Sub or use it as a cell formula.

e.g.: =customSort(A2)

Upvotes: 3

Related Questions