Need a special kind of "sort"...

[RASelkirk]

Merlin & Ron,

I got back to work on this after I posted and came up with a VB
solution that works every time (and fully customizable too). I thin
it's similar to both of your solutions...

(itemList is a two dimensional array defined in a previous step)

' my custom sort "dictionary"
testStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"

ReDim newArray(1, UBound(itemList, 2))
' Run characters of the array against testStr and find it'
position in testStr
For X = 1 To UBound(itemList, 2)
' Convert each of the three positions in the 1st matrix to
string
TempSortNum1 = CStr(InStr(1, testStr, Mid(itemList(0, X), 1
1), vbBinaryCompare))
If Len(TempSortNum1) = 1 Then TempSortNum1 = "0"
TempSortNum1
TempSortNum2 = CStr(InStr(1, testStr, Mid(itemList(0, X), 2
1), vbBinaryCompare))
If Len(TempSortNum2) = 1 Then TempSortNum2 = "0"
TempSortNum2
TempSortNum3 = CStr(InStr(1, testStr, Mid(itemList(0, X), 3
1), vbBinaryCompare))
If Len(TempSortNum3) = 1 Then TempSortNum3 = "0"
TempSortNum3
' combine the strings into a 6 digit number
SortIDX = TempSortNum1 & TempSortNum2 & TempSortNum3
' swap the (meaningless) 2nd matrix with the new 6 digit numbe
(string)
newArray(0, X) = itemList(0, X)
newArray(1, X) = SortIDX
' load both matices into cell columns
Worksheets("Codes").Cells(X + 1, 18) = newArray(0, X)
Worksheets("Codes").Cells(X + 1, 19) = newArray(1, X)
Next
' sort by column "19"
Application.Range("R1:S200").sort Cells(1, 19), xlAscending, , ,
, , xlYes

And then I paste the values of column 18 (the original 3 element codes
now properly sorted) into a defined grid. As you can see, the value fo
"testStr" can be arranged into any order you need..

 

[Ron Rosenfeld]

Merlin & Ron,

I got back to work on this after I posted and came up with a VBA
solution that works every time (and fully customizable too). I think
it's similar to both of your solutions...

I'm glad you got your problem figured out.


--ron