Help with Sorting...

[Dervish]

Hello - I need to have Excel sort the following example of cells:

10-10 Apple 3
10-11 Pale Apple 2
10-12 Pale Apple 1
10-3 Garden Hedge
10-4 Silver Birch 2
10-5 Silver Birch 1
10-6 Pale Birch
1-1 Milk Sugar
1-10 White Mint
1-11 Icicle
11-10 Apple
11-11 Pale Moss 2
11-12 Pale Moss 1
1-12 Morning Frost
11-4 Olive 3
11-5 Olive 2
11-6 Olive 1
1-2 Natural White
etc...

to something like this (small to large):

1-1 Milk Sugar
1-2 Natural White
1-10 White Mint
1-11 Icicle
1-12 Morning Frost
10-3 Garden Hedge
10-4 Silver Birch 2
10-5 Silver Birch 1
10-6 Pale Birch
10-10 Apple 3
10-11 Pale Apple 2
10-12 Pale Apple
11-4 Olive 3
11-5 Olive 2
11-6 Olive 1
11-10 Apple
11-11 Pale Moss 2
11-12 Pale Moss 1
etc...

I did change the cells format from text to number and vice versa but
nothing work so far.

Thanks in advance.

 

[Bernd Plumhoff]

Apply the attached user defined function, use it in a
helper column and sort by that column.

This function comes without ANY warranty or guarantee...

HTH,
Bernd
--
Private Const delim As String = "-" 'Define your delimiter
Private Const basis As Double = 100000
' Now set logbasis = log10(basis) = ln(basis)/ln(10)
Private Const logbasis As Long = 5

Public Function chpt2str(chapter As String) As String

' With given delim = "." and logbasis = 2 chpt2str would
' turn "4.33.12.1" into "04331201" so that you can sort
' chapterwise.
' www.bplumhoff.de

Dim strtmp As String ' Variable to analyze chapter
Dim delimlen As Integer ' Helps to get rightmost partial
number

chpt2str = ""
strtmp = Trim(chapter) ' Delete heading and trailing
blanks

' We walk from right to left and shift all detected numbers
' into result.

delimlen = InStr(1, StrReverse(strtmp), delim,
vbTextCompare)
' Position of rightmost delimiter

Do While delimlen > 0 ' While we have found a delimiter ...

chpt2str = Format(Right(strtmp, delimlen - 1), _
String(logbasis, "0")) & chpt2str
' Shift rightmost number into result
strtmp = Left(strtmp, Len(strtmp) - delimlen)
' Delete rightmost number
delimlen = InStr(1, StrReverse(strtmp), delim,
vbTextCompare)

Loop

chpt2str = Format(strtmp, String(logbasis, "0")) & chpt2str

End Function

 

[Max]

Perhaps you may like to try this as well ..

Assuming this data is in col A, A1 down

10-10 Apple 3
10-11 Pale Apple 2
10-12 Pale Apple 1
10-3 Garden Hedge
10-4 Silver Birch 2

etc

Put in B1: =LEFT(A1,SEARCH("-",A1)-1)+0

Put in C1:
=MID(A1,SEARCH("-",A1)+1,SEARCH(" ",A1)-SEARCH("-",A1))+0

Select cols A to C
Click Data > Sort
Make the settings:
Sort by: Col B (Ascending)
Then by Col C (Ascending)
(No header row)
Click OK

The results returned in col A would be:

1-1 Milk Sugar
1-2 Natural White
1-10 White Mint
1-11 Icicle
1-12 Morning Frost
10-3 Garden Hedge
10-4 Silver Birch 2

etc

 

[Max]

Sorry, missed out a line after:

Put in B1: =LEFT(A1,SEARCH("-",A1)-1)+0

Put in C1:
=MID(A1,SEARCH("-",A1)+1,SEARCH(" ",A1)-SEARCH("-",A1))+0

Select B1:C1 and copy down

 

[Dervish]

Max:

Brilliant!! Thank you very much. It works perfectly. You're making my day!

Thanks.

Dervish

 

[Max]

You're welcome !
Thanks for the feedback

 

[Myrna Larson]

Just a comment on the reason it doesn't sort correctly: you haven't used
leading zeroes. If you enter as 01-06 instead of 1-6, it would sort.

 

[Dervish]

Bernd:

Thank you for helping me. Your solution looks a bit too complicated
for a novice like me.

Best regards,

Dervish