VBA - Get sort order from alphanumeric list

[Legacy 143009]

Hi!

What would be the best practice to get the following result from the sample arrays below:

A2, A10, B8 => Expected result: 1,2,3
B6, C3, A7 => Expected result: 2,3,1
C12, C2, B9 => Expected result: 3,2,1

and so on.. You got the idea I guess. I can't share a sample code because I have no idea. Sorting alphanumerics is already a challenge. Excel normally understands A10, A2, A3 as sorted values, which is not in this case. Reverse engineering it and finding the order is another story. I will be glad if you can help.

 

[KOKOSEK]

You got the idea I guess.

To be honest, completely not.

 

[Drrellik]

is your data in one cell or a row/column?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

you can post some of your table or data so others can help

 

[Legacy 143009]

Sub Test()
  Dim myArray(1 to 3) as String, result As String

  myArray(1) = "A2"
  myArray(2) = "A10"
  myArray(3) = "B8"

  'I need some code here

  Debug.Print result
  '1,2,3
End Sub

or

Sub Test()
  Dim myArray(1 to 3) as String, result As String

  myArray(1) = "B6"
  myArray(2) = "C3"
  myArray(3) = "A7"

  'I need some code here

  Debug.Print result
  '2,3,1
End Sub

or

Sub Test()
  Dim myArray(1 to 3) as String, result As String

  myArray(1) = "C12"
  myArray(2) = "C2"
  myArray(3) = "B9"

  'I need some code here

  Debug.Print result
  '3,2,1
End Sub

 

[Legacy 143009]

Ok, I've managed with a little mess. I am open for better solutions:

Sub test2()
  Dim myArray(1 To 3) As String, result As String, tempArr() As String

  myArray(1) = "B6"
  myArray(2) = "C3"
  myArray(3) = "A7"
  tempArr = myArray
 
  For i = 1 To 2
    For j = i + 1 To 3
      If tempArr(i) > tempArr(j) Then
        temp = tempArr(j)
        tempArr(j) = tempArr(i)
        tempArr(i) = temp
      End If
    Next
  Next
  For i = 1 To 2
    For j = i + 1 To 3
      If Left(tempArr(i), 1) = Left(tempArr(j), 1) Then
        If CInt(Right(tempArr(i), Len(tempArr(i)) - 1)) > CInt(Right(tempArr(j), Len(tempArr(j)) - 1)) Then
          temp = tempArr(j)
          tempArr(j) = tempArr(i)
          tempArr(i) = temp
        End If
      End If
    Next
  Next
  For Each arrItem In myArray
    For i = 1 To 3
      If tempArr(i) = arrItem Then
        result = result & CStr(i) & ","
      End If
    Next
  Next
  result = Left(result, Len(result) - 1)
  MsgBox result
End Sub