Solved: SORT of need help

[Dave]

Probably simple, but I can't seem to get the following to sort...
BIN1(0)
BIN2(0)
etc.
BIN9(0)
When BIN10(0) is added to this "SortRange" (eg. above) it inserts itself following BIN1(0) instead of at the bottom. Here's the sort code I'm using. Thanks. Dave

Set Sortrange = Range("sheet1!FP1:FS" & [Sheet1!Fo1])
With Sortrange
.Sort Key1:=Range("FR1"), Order1:=xlAscending, _
Header:=xlNo, MatchCase:=True, Orientation:=xlTopToBottom
End With

[Demosthine]

Good Morning.

Your code is working correctly in terms of the way a Sort feature works.

When the Sort Method is processing data, it takes the string and compares it character by character, essentially. So taking your values, it will compare as follows.

Bin1(0) vs Bin2(0)
B = B => True
- Don't change the order
Bi = Bi => True
- Don't change the order
Bin = Bin => True
- Don't change the order
Bin1 = Bin2 => False
- Bin1 < Bin2 => True
- Bin1 is placed before Bin2

Now, take the same comparison:
Bin2(0) vs Bin10(0)
B = B => True
- Don't change the order
Bi = Bi => True
- Don't change the order
Bin = Bin => True
- Don't change the order
Bin2 = Bin1 => False
- Bin2 < Bin1 => False
- Bin2 > Bin1 => True
- Bin1 is placed before Bin2


There a few options to fix this.
01.) Perhaps the easiest is to take your Bin Number (1-9) and convert them into two-digit numbers. So you'll have Bin01(0) and Bin02(0). This will correct the automatic sort.
02.) Create a custom bubble sort that splits your values into separate parts and compares your numbers only.

strvalue = "Bin1(0)"

strprefix = Left(strvalue, 3)
strsuffix = Right(strvalue, 3)
intnumber = Mid(strvalue, Len(strprefix) + 1, Len(strvalue) - Len(strprefix) - Len(strsuffix))


Hope this helps.
Scott

[Dave]

Thanks Scott for the assistance. Thanks for the Bubble sort suggestion, however the naming used (eg. BIN1(0) ) is inputted by the user and could be anything and not necessarily numbered. I'm guessing that whatever name is used will be repeated and numbered by the user (ie. Tank1(0), Tank2(0) etc.). Maybe this won't easily be resolved without controlling the naming input? Dave

[Demosthine]

Afternoon again.

If the data is inconsistent like you mentioned, it poses a lot of problems for doing any kind of reliable sort.

Since you mentioned they may not even have numbers in them, I'm afraid I'm somewhat at a loss for a solution, then. My only recommendation would be to go through and do the naming Input. If you knew it would have numbers in it and all of the prefix would be the same, I could provide you another solution.

I'm afraid this comes down to deciding what you want to force upon the user. Automatic sorting that *may* not provide perfect results or forced naming conventions.

Let me know what you come up with. I'm more than willing to help if you can narrow this down some more.

Scott

[Dave]

Scott thank you very much for your assistance which has furthered my SORT understanding. I have decided to do away with the nicety of having the user's list sorted and instead simply have each new item in the list added to the bottom of the list. Being that I anticipate the user will use the same name with numbers (like my eg.'s) then this will suffice. I will mark this solved. Again, thanks and have a nice day. Dave